Using CharIndex to get a SubString

If you need to extract part of a string based on the index of a character in SQL, then you can use the CharIndex function and the Substring function. This works the same as the Find & Mid functions in ColdFusion and IndexOf in JavaScript.

For example  the order_deliverycountry field holds values in the format ‘UK|United Kingdom’. To get the ‘UK’ part use the following code:

select SubString([Orders].order_deliverycountry, 1, CharIndex('|', Orders.order_deliverycountry) - 1) as CountryCode
from [Countries]