Tuesday, August 29, 2006

SQL Server INSTR Equivalent

Access Database queries support the use of INSTR to search inside a datafield for a series of characters - INSTR(DataField, String):

INSTR(DataField, 'y')

In the above example, if the value in DataField was 'yellow', the returned value would be a 1, as 'y' is the first character of the datafield. Using the same value (yellow), INSTR(DataField, 'o') would return a 5.

INSTR, however, doesn't work in SQL Server. SQL Server does, however, have an equivalent - the CHARINDEX - the only difference being that that order of the parameters is reversed - CHARINDEX(String, DataField):

CHARINDEX('y', DataField)


Anonymous said...

Thank you, very useful.

Balaji Gopkirshna said...

good work man

Anonymous said...

How can you search for nth occurence of a particular character in the string?