Link to home
Start Free TrialLog in
Avatar of Gryff
Gryff

asked on

instr() for SQL?

Just a quick question. I need a way to strip part of a field out, the amount I need to strip out is variable but it always the last part of the string/field. I need to do this purely within SQL.

Ideally I want something similar to the basic Instr() command that will let me know when the part I need to strip  starts (in this case the part I need to remove always starts with 'chase' but can and most likely will have characters after it. Once I have that I can use mid, left or any number of things to do the stripping itself.

Any thoughts?
ASKER CERTIFIED SOLUTION
Avatar of arbert
arbert

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of arbert
arbert

You can also use REPLACE to get rid of 'chase'

replace(yourstring,'chase','')

Brett
Avatar of Gryff

ASKER

Replace is no good as I need to remove the text after it as well, but CHARINDEX is exactly what I needed, thank you.
Avatar of Scott Pletcher
Yep, as in:

SELECT LEFT(yourColumn, CHARINDEX('chase', yourColumn) - 1)


If 'chase' may not be present in the column, you will need to do this to prevent errors:

SELECT LEFT(yourColumn, CASE WHEN CHARINDEX('chase', yourColumn) > 0 THEN CHARINDEX('chase', yourColumn) - 1 ELSE LEN(yourColumn) END)

charindex, patindex will both provide you the offset.  

You can use LIKE to decide if it exists as well.

select * from table1
where field like '%chase%'