Sub string in where clause

[KOVMoe]

Why can i not do this? What would be the best way to do this?

select T.REG_NR
,T.DIS_NR
,T.PKG_DEL_CTR_NR
,T.PKG_TCK_NR
,SUBSTRING ( t.pkg_tck_nr,9,2)as test
,T.PKG_DEL_DT
,T.PKG_DEL_RSL_CD
,T.SHR_ID_INF_TE
,T.PKG_DEL_TM
,T.CNS_NA
,T.PKG_XCP_RPT_DT
,T.PKG_XCP_RPT_TM
,T.PKG_XCP_RPT_OGZ_NR
,T.USR_NR
,T.UTC_TCF_MT_QY
,T.PKG_XCP_RSN_CD
,T.PKG_DTN_OGZ_NR

from dbo.tsphtemp t
where SUBSTRING(t.pkg_tck_nr,9,2)='01'
and t.cns_na like '%ibm%'
or t.cns_na like '%hold%'
or t.cns_na like '%hp%'
or t.cns_na like '%sp%'
or t.cns_na like '%pick%'
or t.cns_na like '%hold %'




Thank you,

Moe-King of the Village Idiots.

"When in trouble,
when in doubt;
Run in circles-
SCREAM & SHOUT!!"
Burma Shave

 

[SQLBill]

Why can't you do it? It's valid syntax. What error are you getting? Or are you just not getting the results you expect?

If you aren't getting the results you expect - what datatype is t.pkg_tck_nr? Also, run this and post the result:

SELECT TOP 5 SUBSTRING(t.pkg_tck_nr,9,2)
FROM dbo.tsphtemp

Something else to test, change your where to this:

where     SUBSTRING(t.pkg_tck_nr,9,2)='01'
        and (t.cns_na like '%ibm%'
        or  t.cns_na like '%hold%'
        or  t.cns_na like '%hp%'
        or  t.cns_na like '%sp%'
        or  t.cns_na like '%pick%'
        or  t.cns_na like '%hold %')

-SQLBill

Posting advice: FAQ481-4875

 

[KOVMoe]

I was not getting the results I wanted, when I put the () in the or statement it worked great! Thanx for the quick help.

Thank you,

Moe-King of the Village Idiots.

"When in trouble,
when in doubt;
Run in circles-
SCREAM & SHOUT!!"
Burma Shave