How to extract XX-XXXXXXX or XXX-XX-XXXX

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)

How to extract XX-XXXXXXX or XXX-XX-XXXX

Author

Topic

kumar1248
Starting Member

20 Posts

Posted - 2009-07-13 : 16:44:52

Hi Team,

How to extract XX-XXXXXXX or XXX-XX-XXXX formated data from a Varchar column, Where X = Any Integer value?

Thanks,
Kumar.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-13 : 16:46:47

Use PATINDEX.

N 56°04'39.26"
E 12°55'05.63"

kumar1248
Starting Member

20 Posts

Posted - 2009-07-13 : 16:59:56

Hi Peso,
Thanks for prompt response.
Can you please write a sample query ? I am having confusion using this PATINDEX

thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-13 : 17:04:02

What is your confusion after reading Books Online (SQL Server Help File) about PATINDEX?
Tell me which part of PATINDEX you don't fully understand, and I'll help you.

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-13 : 17:08:50

[code]DECLARE @Sample TABLE
(
Data VARCHAR(300)
)

INSERT @Sample
SELECT 'elömer wer e rwer 12-3456789 wer er wer we r' UNION ALL
SELECT 'Peso' UNION ALL
SELECT '123-45-6789 wert r'

SELECT Data,
CASE
WHEN p1 > 0 THEN SUBSTRING(Data, p1, 10)
WHEN p2 > 0 THEN SUBSTRING(Data, p2, 11)
ELSE NULL
END AS Extracted
FROM (
SELECT Data,
PATINDEX('%[0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9]%', Data) AS p1,
PATINDEX('%[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]%', Data) AS p2
FROM @Sample
) AS d[/code]

N 56°04'39.26"
E 12°55'05.63"

kumar1248
Starting Member

20 Posts

Posted - 2009-07-13 : 19:04:25

Hi Peso,
I worked with PATINDEX and achived the result.

Thanks very much.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources