Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 SUBSTR EXTRACT

Author  Topic 

mchilds1
Starting Member

5 Posts

Posted - 2013-05-16 : 11:11:35
How do I extract substring between 2nd and 3rd hyphan from string containing 6 hyphans

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-05-16 : 11:16:40
Here is an example of how you can do it. This always assumes that you have at least two hyphens. If not, this will fail
DECLARE @x VARCHAR(32) = 'abcd-efgh-ijkl-mnop-qrst-uvw-xyz';

SELECT LEFT( STUFF(@x,1,CHARINDEX('-',@x),''),
CHARINDEX('-',STUFF(@x,1,CHARINDEX('-',@x),''))-1)
Go to Top of Page

mchilds1
Starting Member

5 Posts

Posted - 2013-05-16 : 11:38:50
This is really close, but it returned the substring between the 1st and 2nd hyphans and I need the substring between the 2nd and 3rd hyphans
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-05-16 : 12:04:39
The code I posted above can be expanded to pick up the text between second and third hyphens, but it will be ugly to look at and hard to read. Instead, what I would do is to get a string splitter function and use it. There are many available on the web - one I particularly like is Jeff Moden's (see in Figure 21 here: http://www.sqlservercentral.com/articles/Tally+Table/72993/ ) Copy that code, run it to install the function and use it like this:
DECLARE @x VARCHAR(32) = 'abcd-efgh-ijkl-mnop-qrst-uvw-xyz';

SELECT Item FROM dbo.DelimitedSplit8K(@x,'-') WHERE ItemNumber = 3;
Go to Top of Page

mchilds1
Starting Member

5 Posts

Posted - 2013-05-16 : 13:51:53
James, Even though it might be ugly looking code, I'd rather use std. SQL code than some special function that I have to download.
Go to Top of Page

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-05-16 : 14:26:06
Try this:

[CODE]

DECLARE @x VARCHAR(32) = 'abcd-efgh-ijkl-mnop-qrst-uvw-xyz';

SELECT LEFT(STUFF(STUFF(@x,1,CHARINDEX('-',@x),''), 1,CHARINDEX('-',@x),''),
CHARINDEX('-',STUFF(@x,1,CHARINDEX('-',@x),''))-1);

[/CODE]
Go to Top of Page

mchilds1
Starting Member

5 Posts

Posted - 2013-05-17 : 07:22:54
This code returns efgh but I am looking to extract ijkl
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-17 : 07:29:35
[code]
DECLARE @x VARCHAR(32) = 'abcd-efgh-ijkl-mnop-qrst-uvw-xyz';

SELECT LEFT(STUFF(STUFF(@x,1,CHARINDEX('-',@x),''),1,CHARINDEX('-',STUFF(@x,1,CHARINDEX('-',@x),'')),''),CHARINDEX('-',STUFF(STUFF(@x,1,CHARINDEX('-',@x),''),1,CHARINDEX('-',STUFF(@x,1,CHARINDEX('-',@x),'')),''))-1)


output
---------------------------
ijkl

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-05-17 : 10:57:52
quote:
Originally posted by mchilds1

James, Even though it might be ugly looking code, I'd rather use std. SQL code than some special function that I have to download.

Jeff Moden is a very well-respected SQL expert, and his function in that article is very widely recommended and used. The source code is on his page, and so you can look at the function and see what it does. If you will be programming using T-SQL on a regular basis, a string splitter function will come in very handy quite frequently.

Nonetheless, if you do want to do this without using the string splitter, what Visakh posted is exactly what I had in mind. Nothing wrong with using it - just that it looks very long; and, if you change your mind and you want to use the string between the third and the fourth hyphens rather than the second and third, then it becomes even longer.
Go to Top of Page

mchilds1
Starting Member

5 Posts

Posted - 2013-05-17 : 13:16:26
[quote]Originally posted by visakh16

Fantastic, this worked like a charm. Thanks so much.
Go to Top of Page

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2013-05-17 : 17:08:48
CROSS APPLY can be extremely helpful here, allowing you to assign a name to an expression.
This, to me, gives you the best of both: no external function calls,
but vastly easier to read, understand and maintain code.

SELECT
LEFT(column_after_second_hyphen, CHARINDEX('-', column_after_second_hyphen) - 1) AS string_between_2nd_and_3rd_hyphen
FROM (
SELECT 'abcd-efgh-ijkl-mnop-qrst-uvw-xyz' AS table_column
) AS test_data
CROSS APPLY (
SELECT CHARINDEX('-', table_column) AS first_hyphen
) AS ca1
CROSS APPLY (
SELECT CHARINDEX('-', table_column, first_hyphen + 1) AS second_hyphen
) AS ca2
CROSS APPLY (
SELECT SUBSTRING(table_column, second_hyphen + 1, LEN(table_column)) AS column_after_second_hyphen
) AS ca3

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-20 : 01:15:00
quote:
Originally posted by mchilds1

[quote]Originally posted by visakh16

Fantastic, this worked like a charm. Thanks so much.


welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

sigmas
Posting Yak Master

172 Posts

Posted - 2013-05-20 : 08:59:02
quote:
Originally posted by visakh16


DECLARE @x VARCHAR(32) = 'abcd-efgh-ijkl-mnop-qrst-uvw-xyz';

SELECT LEFT(STUFF(STUFF(@x,1,CHARINDEX('-',@x),''),1,CHARINDEX('-',STUFF(@x,1,CHARINDEX('-',@x),'')),''),CHARINDEX('-',STUFF(STUFF(@x,1,CHARINDEX('-',@x),''),1,CHARINDEX('-',STUFF(@x,1,CHARINDEX('-',@x),'')),''))-1)


output
---------------------------
ijkl



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs




Hey,
Is not this shorter, better and simpler than your one?
DECLARE @x VARCHAR(32) = 'abcd-efgh-kilo-mnop-qrst-uvw-xyz';

--214 characters
SELECT LEFT(STUFF(STUFF(@x,1,CHARINDEX('-',@x),''),1,CHARINDEX('-',STUFF(@x,1,CHARINDEX('-',@x),'')),''),CHARINDEX('-',STUFF(STUFF(@x,1,CHARINDEX('-',@x),''),1,CHARINDEX('-',STUFF(@x,1,CHARINDEX('-',@x),'')),''))-1)

--138 characters
select stuff(d.t,charindex('-',d.t),len(d.t)-charindex('-',d.t)+1,'')
from (
values (
stuff(@x,1,charindex('-',@x,charindex('-', @x)+1),'')
)
)d(t)



Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-05-20 : 09:41:54
quote:
Hey,
Is not this shorter, better and simpler than your one?


It is! If you are on pre-2008 versions, you could use the following:
DECLARE @x VARCHAR(32) = 'abcd-efgh-kilo-mnop-qrst-uvw-xyz';
select stuff(d.t,charindex('-',d.t),len(d.t)-charindex('-',d.t)+1,'')
from ( SELECT stuff(@x,1,charindex('-',@x,charindex('-', @x)+1),'') ) d(T)
Subconciously in my mind I had the desire to avoid a sub-select because the OP may want to use the query against columns in a table rather than against a scalar variable. (Even in that case, one adapt your query).

Setting aside the number of characters, the bigger advantage with your query that I see is that it is more scalable (i.e., if they want to get the string between the 3rd and fourth hyphens rather than the 2nd and 3rd hyphens).
Go to Top of Page

sigmas
Posting Yak Master

172 Posts

Posted - 2013-05-21 : 02:53:51
quote:
Originally posted by ScottPletcher

CROSS APPLY can be extremely helpful here, allowing you to assign a name to an expression.
This, to me, gives you the best of both: no external function calls,
but vastly easier to read, understand and maintain code.


It can be more simpler and shorter like this:
--440 characters
SELECT
LEFT(column_after_second_hyphen, CHARINDEX('-', column_after_second_hyphen) - 1) AS string_between_2nd_and_3rd_hyphen
FROM (
SELECT 'abcd-efgh-ijkl-mnop-qrst-uvw-xyz' AS table_column
) AS test_data
CROSS APPLY (
SELECT CHARINDEX('-', table_column) AS first_hyphen
) AS ca1
CROSS APPLY (
SELECT CHARINDEX('-', table_column, first_hyphen + 1) AS second_hyphen
) AS ca2
CROSS APPLY (
SELECT SUBSTRING(table_column, second_hyphen + 1, LEN(table_column)) AS column_after_second_hyphen
) AS ca3

--404 characters
SELECT
SUBSTRING(table_column, second_hyphen + 1, third_hyphen-second_hyphen-1) AS string_between_2nd_and_3rd_hyphen
FROM (
SELECT 'abcd-efgh-ijkl-mnop-qrst-uvw-xyz' AS table_column
) AS test_data
CROSS APPLY (
SELECT CHARINDEX('-', table_column) AS first_hyphen
) AS ca1
CROSS APPLY (
SELECT CHARINDEX('-', table_column, first_hyphen + 1) AS second_hyphen
) AS ca2
CROSS APPLY (
SELECT CHARINDEX('-', table_column, second_hyphen + 1) AS third_hyphen
) AS ca3
Go to Top of Page

sigmas
Posting Yak Master

172 Posts

Posted - 2013-05-21 : 03:06:41
quote:
Originally posted by James K

quote:
Hey,
Is not this shorter, better and simpler than your one?


It is! If you are on pre-2008 versions, you could use the following:
DECLARE @x VARCHAR(32) = 'abcd-efgh-kilo-mnop-qrst-uvw-xyz';
select stuff(d.t,charindex('-',d.t),len(d.t)-charindex('-',d.t)+1,'')
from ( SELECT stuff(@x,1,charindex('-',@x,charindex('-', @x)+1),'') ) d(T)
Subconciously in my mind I had the desire to avoid a sub-select because the OP may want to use the query against columns in a table rather than against a scalar variable. (Even in that case, one adapt your query).

Setting aside the number of characters, the bigger advantage with your query that I see is that it is more scalable (i.e., if they want to get the string between the 3rd and fourth hyphens rather than the 2nd and 3rd hyphens).


OP may be like this:
DECLARE @x VARCHAR(32) = 'abcd-efgh-ijkl-mnop-qrst-uvw-xyz';

select substring(@x,charindex('-',@x,charindex('-',@x)+1)+1, charindex('-',@x,charindex('-',@x,charindex('-',@x)+1)+1)-(charindex('-',@x,charindex('-',@x)+1)+1))
Go to Top of Page
   

- Advertisement -