Problem with REPLACE command

[djj55]

Hello, in SQL 2000 I have an UPDATE statement that uses a REPLACE, however it is doing something I did not expect by matching more than I want. To test I am using

SELECT City, CASE WHEN City LIKE 'N %' THEN REPLACE(City, 'N ', 'NORTH ') END 
FROM #Temp1 
WHERE City LIKE 'N %'

--Input:  N RICHLND HILLS
--Output: NORTH RICHLNORTHD HILLS

Why is it matching the N that does not have a space after it?

Do I need something special to check for matching a space?

I have also put in several spaces after the N and it still matched.

Thank you for your assistance.

djj
The Lord is My Shepard (Psalm 23) - I need someone to lead me!

 

[gmmastros]

I didn't get the same result you did.

[tt][blue]
Select Replace('N RICHLND HILLS', 'N ', 'NORTH ')
-- Returns: NORTH RICHLND HILLS
[/blue][/tt]

Since you are looking for a string that starts with N<space>, you know the position of the N. It has to be at the beginning. Because you know the position, you could use the Stuff function.

Ex:

SELECT City, Stuff(City, 1, 1, 'NORTH') As RevisedCityName
FROM #Temp1
WHERE City LIKE 'N %'

You are "stuffing" the string 'NORTH' in to the city column, starting at the first character, while also removing the first character (so you don't end up with NNORTH).

You could also use Stuff(City, 2, 0, 'ORTH'), but I like the first way better because it is more self explanatory.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[djj55]

Thank you for the reply - I will look into the STUFF command.
Given I am changing a lot of things I did not think of the fact that N is the first character. Too close to problem I guess.

I agree that

Select Replace('N RICHLND HILLS', 'N ', 'NORTH ')
-- Returns: NORTH RICHLND HILLS

But try

CREATE TABLE #mytab (City VARCHAR(50))
INSERT INTO #mytab (City) VALUES ('N RICHLND HILLS')
SELECT REPLACE(City, 'N ', 'NORTH ') FROM #mytab 
-- Returns: NORTH RICHLNORTHD HILLS

????

djj
The Lord is My Shepard (Psalm 23) - I need someone to lead me!

 

[gmmastros]

I still don't get the same results that you do. Can you please run this and post the results?

Make sure you run this in your user database.

Select 'TempDB', DATABASEPROPERTYEX('TempDB', 'Collation')
Union All
Select 'User Database', DATABASEPROPERTYEX(DB_NAME(), 'Collation')

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

Just curious.... did you solve your problem yet?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[djj55]

The result of your query was
[tt]
TempDB SQL_Latin1_General_CP1_CI_AS
User Database Latin1_General_CI_AS
[/tt]

However, would that effect an UPDATE command?

Found a solution at XAPRB through a Google search that if I add the N it will work

CREATE TABLE #mytab (City VARCHAR(50))
INSERT INTO #mytab (City) VALUES ('N RICHLND HILLS')
SELECT REPLACE(City, [b][red]N[/red][/b]'N ', [b][red]N[/red][/b]'NORTH ') FROM #mytab
-- Returns: NORTH RICHLND HILLS

djj
The Lord is My Shepard (Psalm 23) - I need someone to lead me!

 

[gmmastros]

Well.... originally I was thinking that it might be a collation issue. I wasn't really expecting a bug in the replace function.

I hate to be the bearer of bad news. Since the collations between your user database and TempDB do not match, you are at a greater risk of getting the following error:

[red]Cannot resolve collation conflict for equal to operation.[/red]

You may know this already, but....

Collations determine how data is sorted and how it is compared. When you create a temp table or table variable, string columns will have the collation that matches your TempDB (not your user database). If you ever use a string column to join between a temp table and a real table, you will likely have collation conflicts.

I say likely, because there is a way to accommodate this potential problem. When you create a temp table or table variable, you can specify the collation for your string columns, like this...

Create Table #Blah(id int, Data VarChar(20) Collate Latin1_General_CI_AS)

Alternatively, you can create the column using the default collation of the user database, like this...

Create Table #Blah(id int, Data VarChar(20) Collate Database_Default)

For example...

declare @Temp 
Table   (
        Col1 VARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AS, 
        Col2 VARCHAR(50) COLLATE Latin1_General_CI_AS)

INSERT INTO @Temp (Col1, Col2) 
VALUES ('x', 'x')

Select * From @Temp Where Col1 = Col2

Anyway... I'm sorry I wasn't able to help you with the replace problem you were having. I'm glad you got your problem resolved.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

For what it's worth....

I have a friend with SQL2008 installed. The bug appears to be fixed in that version.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[djj55]

Yep the collation has bit me many times. I inherited a situation where the instance has one collation and the main database has another. This WILL be corrected when we upgrade to 2008 in a couple of months. The big problem is that we routinely use a second database that uses the instance collation. And of course it has 45 million records to compare so no index help. Short term solution was to create a new table with the correct collation as the old manager would not let me change things. New manager agrees that all collations should be the same now.

Thanks for the explanation.

djj
The Lord is My Shepard (Psalm 23) - I need someone to lead me!

 

[Jamfool]

Hi,

Try

-- ASCII pattern matching with char column
CREATE TABLE t (col1 char(30));
INSERT INTO t VALUES ('Robert King');
SELECT * 
FROM t 
WHERE col1 LIKE '% King'   -- returns 1 row
drop table t
-- Unicode pattern matching with nchar column
CREATE TABLE t (col1 nchar(30));
INSERT INTO t VALUES ('Robert King');
SELECT * 
FROM t 
WHERE col1 LIKE '% King'   -- no rows returned
drop table t
-- Unicode pattern matching with nchar column and RTRIM
CREATE TABLE t (col1 nchar (30));
INSERT INTO t VALUES ('Robert King');
SELECT * 
FROM t 
WHERE RTRIM(col1) LIKE '% King'   -- returns 1 row
drop table t

http://msdn.microsoft.com/en-us/library/ms179859.aspx

Pattern Matching by Using LIKE
LIKE supports ASCII pattern matching and Unicode pattern matching. When all arguments (match_expression, pattern, and escape_character, if present) are ASCII character data types, ASCII pattern matching is performed. If any one of the arguments are of Unicode data type, all arguments are converted to Unicode and Unicode pattern matching is performed. When you use Unicode data (nchar or nvarchar data types) with LIKE, trailing blanks are significant; however, for non-Unicode data, trailing blanks are not significant. Unicode LIKE is compatible with the ISO standard. ASCII LIKE is compatible with earlier versions of SQL Server.