Simple replace() not working

Question

Simple replace() not working

karen.harker

Grasshopper

Points: 16
More actions
February 2, 2016 at 10:31 am

#308285

I have a table with the column [LC Call Num] (nvarchar(255)), and the table uses the SQL_Latin1_General_CP1_CI_AS Collation.
THere are some values that appear like this:
QA76.9.A25 I54165 2007 vol. 5
I want to remove everything after the year (2007). I can pull all these examples up with this query:
SELECT [lc call num],
FROM Titles_Import
WHERE [LC Call Num] LIKE '% vol.%';
I was trying to use UPDATE query with REPLACE():
UPDATE Titles_Import
SET [LC Call Num] = REPLACE([LC Call Num], ' vol.%','')
WHERE [LC Call Num] LIKE '% vol.%';
The response I get is "93 records affected", but...nothing changes!
I tried applying the REPLACE in a Select query:
SELECT [lc call num], PATINDEX('% vol.%',[LC Call Num]) as Found,
REPLACE([LC Call Num], ' vol.%','') AS TrimmedLCCN,
FROM Titles_Import WHERE [LC Call Num] LIKE '% vol.%';
The resulting output shows TrimmedLCCN as exactly the same as the original [LC Call Num]. So, the right records are found using the WHERE clause, but the REPLACE() isn't working.
I've tried using COLLATE SQL_Latin1_General_CP1_CS_AS, as such, to no avail:
REPLACE([LC Call Num] COLLATE SQL_Latin1_General_CP1_CS_AS, ' vol.%','')
Usually when I'm stumped, it turns out to be something so simple - what am I missing?
Thanks for any and all help.
Karen Harker

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

John Rowan SSC Guru Points: 56440 More actions · Answer 1

REPLACE does not allow for wildcards. What you'll need to do is identify the starting position for where you want to replace and either use LEFT or SUBSTRING to update the value to the correct result.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

You're trying to replace using the wildcard, which is not a wildcard in the REPLACE function.

Here's an example of possible corrections:

CREATE TABLE #Titles_Import(

[LC Call Num] nvarchar(255) collate SQL_Latin1_General_CP1_CI_AS )

;

INSERT INTO #Titles_Import VALUES('QA76.9.A25 I54165 2007 vol. 5 ')

SELECT [lc call num]

FROM #Titles_Import

WHERE [LC Call Num] LIKE '% vol.%';

--Using your logic with REPLACE

BEGIN TRAN;

UPDATE #Titles_Import

SET [LC Call Num] = REPLACE([LC Call Num], ' vol.','')

WHERE [LC Call Num] LIKE '% vol.%';

SELECT [lc call num]

FROM #Titles_Import ;

ROLLBACK TRAN;

--Actually removing everything starting at ' vol.'

UPDATE #Titles_Import

SET [LC Call Num] = LEFT([LC Call Num], CHARINDEX(' vol.',[LC Call Num]))

WHERE [LC Call Num] LIKE '% vol.%';

SELECT [lc call num]

FROM #Titles_Import ;

DROP TABLE #Titles_Import;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

karen.harker Grasshopper Points: 16 More actions · Answer 3

I knew it would be something simple...Thank you both for your replies. I will pursue using LEFT(), etc.

Karen