SQL 2008 InStr

I have a table that contains a field called StateCode:
My state codes reflect USA, Mexico, Canada etc.. so state codes could be larger than 2 positions.



AB-AAAAAAAAAA
ABC-BBBBB
ABCD-MMMM
K-KKKKKK

I need to update another table using information from this table.

In a Where statement, I need to be able to compare everything to the left of the - to something and I also need to compare everything to the right of the - to something else.

I tried doing this but get   'InStr' is not a recognized built-in function name.

WHERE Substring(StateCode,1,(InStr(StateCode,"-")-1)) = StateProvinceCode and Substring(StateCode,(InStr(StateCode,"-")+1),50) = StateProvinceCountryCode

Is there another way to accomplish this ?
thayduckProgrammer AnalystAsked:
Who is Participating?
 
sognoctConnect With a Mentor Commented:
use charindex instead of instr

usage :
CHARINDEX('-',StateCode)
0
 
Ephraim WangoyaCommented:
You can use LEFT and RIGHT,
Try this way
where LEFT(@t, CHARINDEX('-', @t, 1)-1) = StateProvinceCode 
and RIGHT(@t, CHARINDEX('-', REVERSE(@t), 1)-1) = StateProvinceCountryCode

Open in new window

0
 
Ephraim WangoyaConnect With a Mentor Commented:
replace the @t with StateCode

where LEFT(StateCode, CHARINDEX('-', StateCode, 1)-1) = StateProvinceCode 
and RIGHT(StateCode, CHARINDEX('-', REVERSE(StateCode), 1)-1) = StateProvinceCountryCode

Open in new window

0
 
thayduckProgrammer AnalystAuthor Commented:
Thanks..
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.