Solved

instr() for SQL?

Posted on 2004-03-25
5
10,885 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:ScottPletcher
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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now