how to use substring and charindex to extract desired string

Question

Post reply

how to use substring and charindex to extract desired string

MaggieW

SSC Eights!

Points: 947
More actions
July 9, 2014 at 10:51 pm

#290703

Hi, All
I have a column that contains the follwoing string I need to compare.
ek/df/cv/
ek/df/cv/f
All fields bfore the third / are not fixed but behind the third/ is eiter nothing or one letter
I need a function to extract all the fields before the third / to compare if they are equal.
I can't do it by using the combination of Substring() and charindex() and Len()
Anyone has any good ideas?
Thank you!

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Sachin Nandanwar SSCertifiable Points: 7699 More actions · Answer 1

This ?

DECLARE @STR VARCHAR(40)='ek/df/cv/'

DECLARE @strreverse VARCHAR(40)=Reverse(@str)

SELECT Substring(@strreverse, 1, Charindex('/', @strreverse) - 1)

--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

SELECT

MyString,

x.p1, y.p2, z.p3,

LeftBitty = LEFT(MyString,NULLIF(z.p3,0)-1)

FROM (VALUES ('ek/df/cv/'), ('ek/df/cv/f'), ('ek/df/cvf')) d (MyString)

CROSS APPLY (SELECT p1 = CHARINDEX('/',MyString,0)) x

CROSS APPLY (SELECT p2 = CHARINDEX('/',MyString,p1+1)) y

CROSS APPLY (SELECT p3 = CHARINDEX('/',MyString,p2+1)) z

SELECT

MyString,

z.p3,

LeftBitty = LEFT(MyString,NULLIF(z.p3,0)-1)

FROM (VALUES ('ek/df/cv/'), ('ek/df/cv/f'), ('ek/df/cvf')) d (MyString)

CROSS APPLY (SELECT p3 = CHARINDEX('/',MyString,CHARINDEX('/',MyString,CHARINDEX('/',MyString,0)+1)+1)) z

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

David Burrows SSC Guru Points: 65142 More actions · Answer 3

LEFT([string],LEN([string]) - CASE WHEN RIGHT([string],1)='/' THEN 1 ELSE 2 END)

Far away is close at hand in the images of elsewhere.
Anon.

sgmunson SSC Guru Points: 110640 More actions · Answer 4

And here's a mashup of all 3 methods presented, with full aliasing and slightly changed field names, so that all the methods can be more easily compared. I removed the string position values as they aren't all that necessary given the CHARINDEX methods involved.

--====================================================================================

--METHOD 1

--====================================================================================

SELECT d.SourceString,

LeftPart = LEFT(d.SourceString, NULLIF(z.p, 0) - 1),

RightPart = NULLIF(RIGHT(d.SourceString,1), '/')

FROM (VALUES ('ek/df/cv/'), ('ek/df/cv/f'), ('ek/df/cvf/')) AS d (SourceString)

CROSS APPLY (SELECT p = CHARINDEX('/', d.SourceString, 0)) AS x

CROSS APPLY (SELECT p = CHARINDEX('/', d.SourceString, x.p + 1)) AS y

CROSS APPLY (SELECT p = CHARINDEX('/', d.SourceString, y.p + 1)) AS z

--====================================================================================

--METHOD 2

--====================================================================================

SELECT d.SourceString,

LeftPart = LEFT(d.SourceString, NULLIF(z.p, 0) - 1),

RightPart = NULLIF(RIGHT(d.SourceString, 1), '/')

FROM (VALUES ('ek/df/cv/'), ('ek/df/cv/f'), ('ek/df/cvf/')) AS d (SourceString)

CROSS APPLY (

SELECT p = CHARINDEX('/', d.SourceString,

CHARINDEX('/', d.SourceString,

CHARINDEX('/', d.SourceString, 0) + 1) + 1)

) AS z

--====================================================================================

--METHOD 3

--====================================================================================

SELECT d.SourceString,

LEFT(d.SourceString, LEN(d.SourceString)

- CASE WHEN RIGHT(d.SourceString,1) = '/' THEN 1 ELSE 2 END) AS LeftPart,

NULLIF(RIGHT(d.SourceString, 1), '/') AS RightPart

FROM (VALUES ('ek/df/cv/'), ('ek/df/cv/f'), ('ek/df/cvf/')) AS d (SourceString)

--====================================================================================

They all produce identical results. With this as a base, you might easily construct a testing method based on a large volume of strings that need this kind of desconstruction, to see which one performs best for your situation.

FYI, I did change the last input string to conform to an "apparent standard" that has all strings having 3 slashes. By that, I mean it appears to be the standard, so if it's not, let me know.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

MaggieW SSC Eights! Points: 947 More actions · Answer 5

Thank you all your gurus for all the beautifully written solns and I adopted Sachin's one in the end!

Thank you again!

sgmunson SSC Guru Points: 110640 More actions · Answer 6

MaggieW (7/10/2014)
Thank you all your gurus for all the beautifully written solns and I adopted Sachin's one in the end!
Thank you again!

I'm at a loss to understand, as his query produces an empty string, representing only the lack of characters at the end of the string he chose to deconstruct. Here's his query again:

DECLARE @STR VARCHAR(40)='ek/df/cv/'

DECLARE @strreverse VARCHAR(40)=Reverse(@str)

SELECT Substring(@strreverse, 1, Charindex('/', @strreverse) - 1)

How does that help you compare the 3 portions prior to the last slash?

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

MaggieW SSC Eights! Points: 947 More actions · Answer 7

sgmunson (7/10/2014)
MaggieW (7/10/2014)
Thank you all your gurus for all the beautifully written solns and I adopted Sachin's one in the end!
Thank you again!
I'm at a loss to understand, as his query produces an empty string, representing only the lack of characters at the end of the string he chose to deconstruct. Here's his query again:
DECLARE @STR VARCHAR(40)='ek/df/cv/'
DECLARE @strreverse VARCHAR(40)=Reverse(@str)
SELECT Substring(@strreverse, 1, Charindex('/', @strreverse) - 1)
How does that help you compare the 3 portions prior to the last slash?

HI STEVE

I didn't use his code instead Sachin's code reminds me that I can make use of the reverse function.

The following is what I did in my query:

Select t1.*

From Table t1 join Table t2 on

substring(reverse(t1.col),1, len(t1.col)-Charindex('/', reverse( t1.col))+1)

=

substring(reverse(t2.col),1, len(t2.col)-Charindex('/', reverse( t2.col))+1)

Thank you!

sgmunson SSC Guru Points: 110640 More actions · Answer 8

Thanks for the update. I was truly scratching my head on this until you explained what you did with it.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

MaggieW SSC Eights! Points: 947 More actions · Answer 9

sgmunson (7/11/2014)
Thanks for the update. I was truly scratching my head on this until you explained what you did with it.

You are welcome! Steve

Just found out that I made a mistake typing the code too, 1 should be replaced with Charindex('/', reverse(t.col))

Thanks!