SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Find string and replace		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

tech_1
Posting Yak Master

105 Posts
Posted - 02/16/2013 :  08:32:11  Show Profile  Reply with Quote
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

India
47023 Posts
Posted - 02/16/2013 :  08:48:34  Show Profile  Reply with Quote
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/
Edited by - visakh16 on 02/16/2013 08:49:32
Go to Top of Page

tech_1
Posting Yak Master

105 Posts
Posted - 02/16/2013 :  14:50:26  Show Profile  Reply with Quote
is that not overexcessive?! using XML.... ??
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

1481 Posts
Posted - 02/16/2013 :  15:03:39  Show Profile  Reply with Quote
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

105 Posts
Posted - 02/16/2013 :  15:33:04  Show Profile  Reply with Quote
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

India
47023 Posts
Posted - 02/17/2013 :  01:34:39  Show Profile  Reply with Quote
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

105 Posts
Posted - 02/17/2013 :  11:10:09  Show Profile  Reply with Quote
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

India
47023 Posts
Posted - 02/17/2013 :  11:26:32  Show Profile  Reply with Quote
why not test and post the result?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000