<div>
You can use LEFT and RIGHT,<br />
Try this way<br />

<pre><code id="code-20-38744280-1">where LEFT(@t, CHARINDEX('-', @t, 1)-1) = StateProvinceCode 
and RIGHT(@t, CHARINDEX('-', REVERSE(@t), 1)-1) = StateProvinceCountryCode</code></pre>
</div>


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

<div>
<div class="content wysiwyg-content">
I have a table that contains a field called StateCode:<br />
My state codes reflect USA, Mexico, Canada etc.. so state codes could be larger than 2 positions.<br />
<br />
<br />
<br />
AB-AAAAAAAAAA<br />
ABC-BBBBB<br />
ABCD-MMMM<br />
K-KKKKKK<br />
<br />
I need to update another table using information from this table.<br />
<br />
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.<br />
<br />
I tried doing this but get &nbsp; 'InStr' is not a recognized built-in function name.<br />
<br />
WHERE Substring(StateCode,1,(InS<wbr />tr(StateCo<wbr />de,&quot;-&quot;)-1)<wbr />) = StateProvinceCode and Substring(StateCode,(InStr<wbr />(StateCode<wbr />,&quot;-&quot;)+1),5<wbr />0) = StateProvinceCountryCode<br />
<br />
Is there another way to accomplish this ?
</div>
</div>


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 ?

SQL 2008 InStr

Microsoft SQL Server 2008 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. Major improvements include the  Always On technologies and support for unstructured data types.