SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 SUBSTR EXTRACT
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mchilds1
Starting Member

USA
5 Posts

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

James K
Flowing Fount of Yak Knowledge

3695 Posts

Posted - 05/16/2013 :  11:16:40  Show Profile  Reply with Quote
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

USA
5 Posts

Posted - 05/16/2013 :  11:38:50  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3695 Posts

Posted - 05/16/2013 :  12:04:39  Show Profile  Reply with Quote
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

USA
5 Posts

Posted - 05/16/2013 :  13:51:53  Show Profile  Reply with Quote
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

547 Posts

Posted - 05/16/2013 :  14:26:06  Show Profile  Reply with Quote
Try this:



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);

Go to Top of Page

mchilds1
Starting Member

USA
5 Posts

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

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 05/17/2013 :  07:29:35  Show Profile  Reply with Quote

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
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3695 Posts

Posted - 05/17/2013 :  10:57:52  Show Profile  Reply with Quote
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

USA
5 Posts

Posted - 05/17/2013 :  13:16:26  Show Profile  Reply with Quote
[quote]Originally posted by visakh16

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

ScottPletcher
Constraint Violating Yak Guru

USA
407 Posts

Posted - 05/17/2013 :  17:08:48  Show Profile  Reply with Quote
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


Edited by - ScottPletcher on 05/17/2013 17:10:30
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 05/20/2013 :  01:15:00  Show Profile  Reply with Quote
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

Belarus
172 Posts

Posted - 05/20/2013 :  08:59:02  Show Profile  Reply with Quote
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)




Edited by - sigmas on 05/20/2013 09:02:04
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3695 Posts

Posted - 05/20/2013 :  09:41:54  Show Profile  Reply with Quote
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

Belarus
172 Posts

Posted - 05/21/2013 :  02:53:51  Show Profile  Reply with Quote
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

Edited by - sigmas on 05/21/2013 02:57:19
Go to Top of Page

sigmas
Posting Yak Master

Belarus
172 Posts

Posted - 05/21/2013 :  03:06:41  Show Profile  Reply with Quote
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))

Edited by - sigmas on 05/21/2013 03:07:56
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.25 seconds. Powered By: Snitz Forums 2000