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)
 Find string and replace

Author  Topic 

tech_1
Posting Yak Master

129 Posts

Posted - 2013-02-16 : 08:32:11
Hi.

if I have a string, I want to be able to replace the string if the string contains "xxx."

"xxx" is a variable string like so:

"Table1.FieldA,Table2.FieldA,Table3.FieldC......"

so whereever there is a Table1/Table2/Table3 I want that to be replaced with an empty string (including the period)

any ideas?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-16 : 08:48:34
yep.
See below illustration

DECLARE @Str varchar(8000)

SET @Str='Table1.FieldA,Table2.FieldA,Table3.FieldC'

SELECT
REPLACE(CAST(XMLVal.query('data(/Root/Node/FieldName)') AS varchar(1000)),' ',',')
FROM (SELECT CAST('<Root><Node><TableName>' + REPLACE(REPLACE(@Str,'.','</TableName><FieldName>'),',','</FieldName></Node><Node><TableName>') + '</FieldName></Node></Root>' AS xml) AS XMLVal)t


output
---------------------------------
FieldA,FieldA,FieldC



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

tech_1
Posting Yak Master

129 Posts

Posted - 2013-02-16 : 14:50:26
is that not overexcessive?! using XML.... ??
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-16 : 15:03:39
Since T-SQL does not support regular expressions natively, other options I can think of are:

a) Use a CLR Regex - see here for an implementation of CLR Regex. http://www.simple-talk.com/sql/t-sql-programming/clr-assembly-regex-functions-for-sql-server-by-example/

b) Use a string splitter function to split the strings, and reassemble (using FOR XML PATH) only those pieces you want to keep. See here for a string splitter function: http://www.sqlservercentral.com/articles/Tally+Table/72993/

c) Restrict the input string such that you can predict the number of tokens and the nature of tokens and use a series of T-SQL string functions such as STUFF, REPLACE etc.
Go to Top of Page

tech_1
Posting Yak Master

129 Posts

Posted - 2013-02-16 : 15:33:04
Thanks James K. if anyone else has any other options please do post them

yes the CLR is a good option but a little excessive IMHO.

ill settle with this solution and make a UDF :)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-17 : 01:34:39
quote:
Originally posted by tech_1

is that not overexcessive?! using XML.... ??


Otherwise for your scenario you've do a parsing of string get individual pieces out, reove your patterns and rejoin them again to a string

I felt this would be simple as it can be done inline and does not require intermediate parsing step.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

tech_1
Posting Yak Master

129 Posts

Posted - 2013-02-17 : 11:10:09
thanks visakh16 - I appreciate it. Curious to know the perf impact between doing the individual pieces and your XML solution?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-17 : 11:26:32
why not test and post the result?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -