Solved

instr() for SQL?

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

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.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

911 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

20 Experts available now in Live!

Get 1:1 Help Now