March 6, 2015 at 8:40 am
One of those anoying little things..
UPDATE [Staging_TABLE] SET Data_clean = REPLACE(Data_clean, '||','"')
WHERE (Data_clean like '%|,||%') and Data_clean not like '%|,||,|%'
|,|text|,||,|address1|,||Housename" Address2|,|
I am trying to replace the || with " which comes after a , but not if it has a , before it!
Why wont this wrork? any idea? I need it to read
|,|text|,||,|address1|,"Housename" Address2|,|
March 6, 2015 at 8:49 am
SQLAssAS (3/6/2015)
One of those anoying little things..UPDATE [Staging_TABLE] SET Data_clean = REPLACE(Data_clean, '||','"')
WHERE (Data_clean like '%|,||%') and Data_clean not like '%|,||,|%'
|,|text|,||,|address1|,||Housename" Address2|,|
I am trying to replace the || with " which comes after a , but not if it has a , before it!
Why wont this wrork? any idea? I need it to read
|,|text|,||,|address1|,"Housename" Address2|,|
Will the pattern always come before a letter? If so, you might try something like:
UPDATE staging_table
SET Data_clean = REPLACE(Data_clean, '||', '''')
WHERE Data_clean LIKE '%|,||[a-z]'
-SQLBill
March 6, 2015 at 8:52 am
unfortunately not! thanks though
March 11, 2015 at 10:03 am
any help here?
March 11, 2015 at 10:14 am
Maybe try multiple update statements,
UPDATE [Staging_TABLE] SET Data_clean = REPLACE(Data_clean, ',||,','^')
UPDATE [Staging_TABLE] SET Data_clean = REPLACE(Data_clean, ',||',',"')
UPDATE [Staging_TABLE] SET Data_clean = REPLACE(Data_clean, '^',',||,')
March 11, 2015 at 10:21 am
SQLAssAS (3/6/2015)
unfortunately not! thanks though
I don't quite understand the requirements here. How about a couple of examples for input and the desired output?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 11, 2015 at 10:25 am
Be very careful about putting the "when" and "not when" conditions in a where clause. This could cause unexpected results if both conditions exists in the same string.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
March 12, 2015 at 4:16 am
thanks for the advise of putting multiple statements, i ended up with 8!!
I am going to open a seperate thread for this issue
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply