Only Display Specific Portion of a String

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)

Only Display Specific Portion of a String

Author

Topic

callawayx14
Yak Posting Veteran

73 Posts

Posted - 2012-03-01 : 09:38:57

Hello

I'm attempting to display a speccific portion of a string.

SELECT Field_name
FROM tbl
WHERE Field_Name LIKE '%12345%'

Actual data would look like this
'sometextsometext12345sometextsometext'

I would would like the results of my query to only diplay the 12345

I appreciate any help...thank you!!

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-03-01 : 09:40:18

that doesn't make any sense.

If you are looking for the pattern %12345% and you only want to disply the 12345 then the query should be


SELECT '12345' AS 
FROM tbl
WHERE [Field_name] LIKE '%12345%'

I suspect you actually want to do something else.

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

callawayx14
Yak Posting Veteran

73 Posts

Posted - 2012-03-01 : 09:45:18

Thank you for clarifying

the data would really look more like this

sometextsometextMAIL_ID:12345sometextsometext
sometextsometextMAIL_ID:6544478sometextsometext
sometextsometextMAIL_ID:645215648sometextsometext

SELECT Field_Name
FROM tbl
WHERE Field_Name LIKE '%MAIL_ID%'

I would want to diplay the actual ID (or set of numbers following 'Mail_ID'

The numbers can vary in length

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-03-01 : 10:00:14

ok -- that makes more sense.

Something like this


BEGIN TRAN

DECLARE @sample TABLE (
	[value] VARCHAR(512)
	)
INSERT @sample
VALUES ('sometextsometextMAIL_ID:12345sometextsometext')
	,  ('sometextsometextMAIL_ID:6544478sometextsometext')
	,  ('sometextsometextMAIL_ID:645215648sometextsometext')
	, ('')
	, (NULL)
	, ('asadasdMAIL_ID:1')

SELECT
	[RawValue]
	, CASE
		WHEN rp.[trimmedValue] LIKE '%[^0-9]%' THEN LEFT(rp.[trimmedValue], PATINDEX('%[^0-9]%', [rp].[trimmedValue]) - 1)
		ELSE rp.[trimmedValue]
	END AS [Trimmed Value]
FROM
	(
	SELECT
		[value] AS [RawValue]
		, CASE WHEN [value] LIKE '%MAIL_ID:%' THEN RIGHT([value], LEN([value]) - PATINDEX('%MAIL_ID:%', [value]) - 7)
		ELSE NULL
		END  AS [trimmedValue]
	FROM
		@sample
	)
	AS rp
	
ROLLBACK

I've tried to make it safe so that it doesn't die on unexpected strings but your milage may vary.

Results


RawValue                                           Trimmed Value
-------------------------------------------------- --------------------------------------------------
sometextsometextMAIL_ID:12345sometextsometext      12345
sometextsometextMAIL_ID:6544478sometextsometext    6544478
sometextsometextMAIL_ID:645215648sometextsometext  645215648
                                                   NULL
NULL                                               NULL
asadasdMAIL_ID:1                                   1

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

callawayx14
Yak Posting Veteran

73 Posts

Posted - 2012-03-01 : 11:28:28

thank you! very much for this. I really appreciate your time & help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources