SQL String - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

SQL String

Author

Topic

Carpend
Starting Member

7 Posts

Posted - 2010-02-23 : 06:51:41

Hi,

I'm struggling on how to break down below data into a string using substring/charindex.

I've a field in my DB that contains data in the format of between 2-6 Characters then and underscore a set of 3-4 characters then an underscore and then another set of 3 characters

So the field could include

edh_edf_ert
fe_eer_ffg
1772_r4t_rrlk

How do i construct a sql stament that will take the anthing after the last underscore?

So select statement would give me

Restult

ert
ffg
rrlk

Thanks Dave

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-02-23 : 06:58:27

select parsename(replace('1772_r4t_rrlk','_','.'),1)

No, you're never too old to Yak'n'Roll if you're too young to die.

Kristen
Test

22859 Posts

Posted - 2010-02-23 : 06:58:47

[code]DECLARE @MyTable TABLE
(
MyField VARCHAR(20)
)

INSERT INTO @MyTable
SELECT 'edh_edf_ert' UNION ALL
SELECT 'fe_eer_ffg' UNION ALL
SELECT '1772_r4t_rrlk'

SELECT MyField, RIGHT(MyFIeld, CHARINDEX('_', REVERSE(MyField))-1)
FROM @MyTable
[/code]

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-02-23 : 07:01:44

Webfred's solution will break if the string has a period (.) in it.

For example


DECLARE @testTable TABLE (
	[val] VARCHAR(255)
	)

INSERT @testTable
      SELECT 'edh_edf_ert'
UNION SELECT 'fe_eer_ffg'
UNION SELECT '1772_r4t_rrlk'

-- Added
UNION SELECT 'foo'
UNION SELECT '_test1'
UNION SELECT '__TES.T2' -- This will break parsename
UNION SELECT NULL

-- Using Case should be safe
SELECT
	CASE
		WHEN  CHARINDEX('_', REVERSE([val])) = 0 THEN [val]
		ELSE RIGHT([val], CHARINDEX('_', REVERSE([val])) - 1)
	END
FROM
	@testTable


SELECT parsename(replace([val],'_','.'),1) FROM @testTable

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Kristen
Test

22859 Posts

Posted - 2010-02-23 : 07:02:06

Parsename will fail if your field includes any "." ... but is fine otherwise.

No idea on the performance differences between the two approaches

If your field may contain more than "3 parts" (separated by "_") then you need the more complicated:


SELECT	MyField, substring(MyField + '_',  charindex('_', MyField + '_',  charindex('_', MyField + '_') + 1) + 1, 
			charindex('_', MyField + '_',  charindex('_', MyField + '_',  charindex('_', MyField + '_') + 1) + 1) 
			-  charindex('_', MyField + '_',  charindex('_', MyField + '_') + 1) - 1 )
FROM	@MyTable

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-02-23 : 07:02:22

quote:

Originally posted by Kristen

DECLARE @MyTable TABLE
(
	MyField VARCHAR(20)
)

INSERT INTO @MyTable
SELECT 'edh_edf_ert' UNION ALL
SELECT 'fe_eer_ffg' UNION ALL
SELECT '1772_r4t_rrlk'

SELECT MyField, RIGHT(MyFIeld, CHARINDEX('_', REVERSE(MyField))-1)
FROM	@MyTable

This will break for strings without a _ in them.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-02-23 : 07:08:00

Use of CASE should be safe.


DECLARE @testTable TABLE (
	[val] VARCHAR(255)
	)

INSERT @testTable
      SELECT 'edh_edf_ert'
UNION SELECT 'fe_eer_ffg'
UNION SELECT '1772_r4t_rrlk'

-- Added
UNION SELECT 'foo'
UNION SELECT '_test1'
UNION SELECT '__TES.T2' -- This will break parsename
UNION SELECT NULL

-- Using Case should be safe
SELECT
	CASE
		WHEN  CHARINDEX('_', REVERSE([val])) = 0 THEN [val]
		ELSE RIGHT([val], CHARINDEX('_', REVERSE([val])) - 1)
	END
FROM
	@testTable

Seems the posts all got a little mixed up, everyone posting at once.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-02-23 : 07:09:37

just for fun:
select replace(parsename(replace(replace('1772_r4t_rr.lk','.','<dot>'),'_','.'),1),'<dot>','.')

No, you're never too old to Yak'n'Roll if you're too young to die.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-02-23 : 07:12:44

quote:
Originally posted by webfred

just for fun:
select replace(parsename(replace(replace('1772_r4t_rr.lk','.','<dot>'),'_','.'),1),'<dot>','.')

No, you're never too old to Yak'n'Roll if you're too young to die.

This returns NULL on the empty string ('').

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-02-23 : 07:13:50

Uuh baby that's what I like

No, you're never too old to Yak'n'Roll if you're too young to die.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-02-23 : 07:15:24

quote:
Originally posted by webfred

Uuh baby that's what I like

No, you're never too old to Yak'n'Roll if you're too young to die.

Seriously! LOL

That's just the way you roll?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-23 : 07:34:21

quote:
Originally posted by Carpend

Hi,

I'm struggling on how to break down below data into a string using substring/charindex.

I've a field in my DB that contains data in the format of between 2-6 Characters then and underscore a set of 3-4 characters then an underscore and then another set of 3 characters

So the field could include

edh_edf_ert
fe_eer_ffg
1772_r4t_rrlk

How do i construct a sql stament that will take the anthing after the last underscore?

So select statement would give me

Restult

ert
ffg
rrlk

Thanks Dave

If you use front end application, make use of split function
That would be easier than writing code in sql

Madhivanan

Failing to plan is Planning to fail

Carpend
Starting Member

7 Posts

Posted - 2010-02-23 : 09:03:24

Thanks for the help everyone

One last question - is it possible to split it to take just the middle set of characters?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-02-23 : 09:05:10

An approach:
select parsename(replace('1772_r4t_rrlk','_','.'),2)

No, you're never too old to Yak'n'Roll if you're too young to die.

Carpend
Starting Member

7 Posts

Posted - 2010-02-23 : 09:10:03

That's ace thanks! what does the '.' actually do?

Kristen
Test

22859 Posts

Posted - 2010-02-23 : 09:13:28

Or:

substring(MyField + '_', charindex('_', MyField + '_') + 1, charindex('_', MyField + '_', charindex('_', MyField + '_') + 1) - charindex('_', MyField + '_') - 1 )

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-02-23 : 09:17:49

parsename was build to extract the nameparts from an object name (like MyDB.dbo.MyTable) and needs the point to work.
replacing that "_" by a dot is a trick so you can use parsename for this

No, you're never too old to Yak'n'Roll if you're too young to die.

Kristen
Test

22859 Posts

Posted - 2010-02-23 : 10:33:28

Probably not relevant, but parsename will only work on string up to 128 characters long. It can only split out, at most, four pieces.

I personally think this is relying on the effects of the function parsename which was not intended for this purpose, and thus don't use it for this type of job. But that's just me!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-23 : 10:33:49

quote:
Originally posted by webfred

parsename was build to extract the nameparts from an object name (like MyDB.dbo.MyTable) and needs the point to work.
replacing that "_" by a dot is a trick so you can use parsename for this

No, you're never too old to Yak'n'Roll if you're too young to die.

Also note that it has restriction of maximum of four parts

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-23 : 10:34:44