Get the string value found by regular expression

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

SQL Server Development (2000)

Get the string value found by regular expression

Author

Topic

NickStan
Starting Member

36 Posts

Posted - 2009-03-25 : 06:03:54

Hi Guys

I am trying to get the value of the string found by a regular expression.

For example:

If this statement
select dbo.regexFind('fdjakfda jfdalf 123456', '\d{6}', 1)
evaluates to 1, bring back the value of the string that caused the regular expression to evaluate to true.

In other words, bring back the value: 123456

Thanks

Nick

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-19 : 10:40:13

No need for regular expression here.
Just PATINDEX and you're fine

DECLARE	@Sample TABLE
	(
		Data VARCHAR(200)
	)

INSERT	@Sample
SELECT	'fdjakfda jfdalf 123456' UNION ALL
SELECT	'fdjakfda 987654 jfdalf'

DECLARE	@Digits INT

SET	@Digits = 6

SELECT	Data,
	SUBSTRING(Data, Pos, @Digits) AS Peso
FROM	(
		SELECT	Data,
			PATINDEX('%' + REPLICATE('[0-9]', @Digits) + '%', Data) AS Pos
		FROM	@Sample
	) AS d

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources