Solved

instr() for SQL?

Posted on 2004-03-25
5
10,890 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?
0
Comment
Question by:Gryff
5 Comments
 
LVL 34

Accepted Solution

by:
arbert earned 125 total points
ID: 10678824
See CHARINDEX in books online...
0
 
LVL 34

Expert Comment

by:arbert
ID: 10678833
You can also use REPLACE to get rid of 'chase'

replace(yourstring,'chase','')

Brett
0
 

Author Comment

by:Gryff
ID: 10678858
Replace is no good as I need to remove the text after it as well, but CHARINDEX is exactly what I needed, thank you.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 10678990
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)

0
 
LVL 15

Expert Comment

by:SRigney
ID: 10679036
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%'
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how the fundamental information of how to create a table.

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question