We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

instr() for SQL?

Gryff
Gryff asked
on
Medium Priority
10,918 Views
Last Modified: 2012-08-14
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?
Comment
Watch Question

Top Expert 2004
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
Top Expert 2004

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

replace(yourstring,'chase','')

Brett

Author

Commented:
Replace is no good as I need to remove the text after it as well, but CHARINDEX is exactly what I needed, thank you.
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
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)

Commented:
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%'
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.