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 failDECLARE @x VARCHAR(32) = 'abcd-efgh-ijkl-mnop-qrst-uvw-xyz';SELECT LEFT( STUFF(@x,1,CHARINDEX('-',@x),''), CHARINDEX('-',STUFF(@x,1,CHARINDEX('-',@x),''))-1) |
|
|
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 |
|
|
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; |
|
|
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. |
|
|
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] |
|
|
mchilds1
Starting Member
5 Posts |
Posted - 2013-05-17 : 07:22:54
|
This code returns efgh but I am looking to extract ijkl |
|
|
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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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. |
|
|
mchilds1
Starting Member
5 Posts |
Posted - 2013-05-17 : 13:16:26
|
[quote]Originally posted by visakh16Fantastic, this worked like a charm. Thanks so much. |
|
|
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_hyphenFROM ( SELECT 'abcd-efgh-ijkl-mnop-qrst-uvw-xyz' AS table_column) AS test_dataCROSS APPLY ( SELECT CHARINDEX('-', table_column) AS first_hyphen) AS ca1CROSS APPLY ( SELECT CHARINDEX('-', table_column, first_hyphen + 1) AS second_hyphen) AS ca2CROSS APPLY ( SELECT SUBSTRING(table_column, second_hyphen + 1, LEN(table_column)) AS column_after_second_hyphen) AS ca3 |
|
|
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 visakh16Fantastic, this worked like a charm. Thanks so much.
welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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 MVPhttp://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 charactersSELECT 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 charactersselect 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) |
|
|
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). |
|
|
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 charactersSELECT LEFT(column_after_second_hyphen, CHARINDEX('-', column_after_second_hyphen) - 1) AS string_between_2nd_and_3rd_hyphenFROM ( SELECT 'abcd-efgh-ijkl-mnop-qrst-uvw-xyz' AS table_column) AS test_dataCROSS APPLY ( SELECT CHARINDEX('-', table_column) AS first_hyphen) AS ca1CROSS APPLY ( SELECT CHARINDEX('-', table_column, first_hyphen + 1) AS second_hyphen) AS ca2CROSS APPLY ( SELECT SUBSTRING(table_column, second_hyphen + 1, LEN(table_column)) AS column_after_second_hyphen) AS ca3--404 charactersSELECT SUBSTRING(table_column, second_hyphen + 1, third_hyphen-second_hyphen-1) AS string_between_2nd_and_3rd_hyphenFROM ( SELECT 'abcd-efgh-ijkl-mnop-qrst-uvw-xyz' AS table_column) AS test_dataCROSS APPLY ( SELECT CHARINDEX('-', table_column) AS first_hyphen) AS ca1CROSS APPLY ( SELECT CHARINDEX('-', table_column, first_hyphen + 1) AS second_hyphen) AS ca2CROSS APPLY ( SELECT CHARINDEX('-', table_column, second_hyphen + 1) AS third_hyphen) AS ca3 |
|
|
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)) |
|
|
|
|
|