How to get a substring from main string with regex

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)

How to get a substring from main string with regex

Author

Topic

phrankbooth
Posting Yak Master

162 Posts

Posted - 2012-12-09 : 15:44:45

I have astring like this:
'THIS,IS -12345(abcdefg,DataSource=TheServerName101;XXXLAKSDJNFKAICNCW)'

from that I'd like to extract: TheServerName101, either side of that string could me larger or smaller, thats why I think maybe regex may be better, But I'm not sure how to do that.

Any help appreciated.

--PhB

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-12-09 : 17:29:55

Unfortunately there is no native support for regular expressions in T-SQL. There are CLR implementations of regex replace etc. on the web - for example see here

For the example you posted, the following (which uses only native T-SQL functions) should work

DECLARE @x VARCHAR(255) = 'THIS,IS -12345(abcdefg,DataSource=TheServerName101;XXXLAKSDJNFKAICNCW)';
SELECT LEFT(STUFF(@x,1,10+CHARINDEX('DataSource=',@x),''),CHARINDEX(';',STUFF(@x,1,10+CHARINDEX('DataSource=',@x),''))-1);

skc40
Starting Member

34 Posts

Posted - 2012-12-10 : 00:22:51

I've had similar situation.

This is what I did

Declare @x varchar(255)
Set @x='THIS,IS -12345(abcdefg,DataSource=TheServerName101;XXXLAKSDJNFKAICNCW)'
Select SubString(@x,PatIndex ('%TheServerName101%',@x),16)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources