May 30, 2012 at 10:28 am
I am trying to replace string data in a table column as follows:
UPDATE table
SET column = REPLACE(column, 'ABC', 'XYZ')
WHERE column LIKE 'ABC%'
This is working ok, except some of the rows have the data in the format 'Abc', with the first letter capitalized. These values are not being replaced, even though my server and database collations are both case-insensitive. Is the REPLACE function or the WHERE clause looking at the case value?
May 30, 2012 at 10:45 am
You may want to do some checking of the data. You may have unprintable characters embedded in som of your data. I just ran a test and everything worked like it should.
CREATE TABLE #Test(TestVal VARCHAR(10));
INSERT INTO #Test
SELECT 'ABC' UNION ALL
SELECT 'Abc' UNION ALL
SELECT 'aBc' UNION ALL
SELECT 'ASD';
SELECT * FROM #Test;
UPDATE #Test SET
TestVal = REPLACE(TestVal,'ABC','XYZ')
WHERE
TestVal LIKE 'ABC%';
SELECT * FROM #Test;
DROP TABLE #Test;
May 30, 2012 at 2:12 pm
Is the column collation Case insensitive? You can set collation per column. Lynn's test modified:
CREATE TABLE #Test(TestVal VARCHAR(10) COLLATE Latin1_General_CS_AS);
INSERT INTO #Test
SELECT 'ABC' UNION ALL
SELECT 'Abc' UNION ALL
SELECT 'aBc' UNION ALL
SELECT 'ASD';
SELECT * FROM #Test;
UPDATE #Test SET
TestVal = REPLACE(TestVal,'ABC','XYZ')
WHERE
TestVal LIKE 'ABC%';
SELECT * FROM #Test;
DROP TABLE #Test;
A simple query like this will tell you:
SELECT
*
FROM
sys.columns AS C
WHERE
C.collation_name LIKE '%CS%'
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 30, 2012 at 3:22 pm
i get the same issue if with SQL server 2012 where it replaces ABC but not Abc, aBc ir any other combination here is how i overcome it
REPLACE(upper(TestVal),'ABC','XYZ')
***The first step is always the hardest *******
May 30, 2012 at 3:27 pm
Thanks for all the advice. I think I have a handle on the problem now.
May 30, 2012 at 3:57 pm
Brian Brown-204626 (5/30/2012)
Thanks for all the advice. I think I have a handle on the problem now.
Be sure to let us know when you resolve the problem and how you fixed it.
May 30, 2012 at 4:44 pm
REPLACE will replace all occurrences of the search string, which may not be what you want. One way to replace just the first occurrence is to use STUFF instead. On the collation issue, you can use a slightly different form of LIKE to retain some SARGability:
CREATE TABLE #Test
(
TestVal varchar(10)
COLLATE SQL_Latin1_General_CP1_CS_AS
NOT NULL
);
CREATE CLUSTERED INDEX cx ON #Test (TestVal);
INSERT #Test
(TestVal)
VALUES
('ABC'),
('Abc'),
('aBc'),
('ASD');
SELECT * FROM #Test AS t;
UPDATE #Test
SET TestVal = STUFF(TestVal, 1, 3, 'XYZ')
WHERE
TestVal LIKE '[Aa][Bb][Cc]%';
SELECT * FROM #Test AS t;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply