November 16, 2011 at 12:39 pm
I have to find " in a filed and change the " to feet i.e. wherever there is " i have to change it to the word feet
I am using
If exists
(select * from X where field like '%"%')
Begin
update X
set field = replace(cast(field as nvarchar(max)),'"','meters')
Print 'Changed'
End
Else
Begin
'not Changed'
END
Is there a better way to recognize double quotes rather than with like as above??
November 16, 2011 at 1:13 pm
You can use REPLACE function.
REPLACE('5"7 tall', '"', 'feet')
November 16, 2011 at 1:40 pm
I am using the replace function, the problem is that it never goes to the else part it will always find the first part of the statement
November 16, 2011 at 1:45 pm
Did someone use two single quotes in stead of double quotes ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 18, 2011 at 8:24 pm
Another way to do the replace is REPLACE(field, CHAR(34), 'feet')
You mentioned it never gets to the ELSE portion of the code. It sounds like you want output for each row which can be handled using a CURSOR or use the OUTPUT clause as part of your UPDATE statement.
One way to do it is by CURSOR which wouldn’t be my first choice. Below is an example using a CURSOR:
SET NOCOUNT ON
CREATE TABLE #tToFeet (
toFeetId INT IDENTITY(1,1)
, Description VARCHAR(50)
)
INSERT INTO #tToFeet
VALUES ('5'' 4" Tale')
, ('No Quotes')
, ('I''ll Put a Quote Here" and here"')
DECLARE @toFeetId INT
, @Description VARCHAR(50)
DECLARE cToFeet CURSOR READ_ONLY
FOR
SELECT toFeetId
, Description
FROM #tToFeet
OPEN cToFeet
FETCH NEXT FROM cToFeet
INTO @toFeetId, @Description
WHILE @@FETCH_STATUS = 0
BEGIN
IF @Description LIKE '%"%'
BEGIN
UPDATE #tToFeet
SET Description = REPLACE(@Description, CHAR(34), 'feet')
WHERE ToFeetId = @toFeetId
PRINT 'Changed'
END
ELSE
BEGIN
PRINT 'Not Changed'
END
FETCH NEXT FROM cToFeet
INTO @toFeetId, @Description
END
CLOSE cToFeet
DEALLOCATE cToFeet
DROP TABLE #tToFeet
SET NOCOUNT OFF
A better approach to the CURSOR is using an OUTPUT clause with the UPDATE statement as in the example below:
UPDATE #tToFeet
SET Description = REPLACE(Description, CHAR(34), 'feet')
OUTPUT inserted.toFeetId
, CASE
WHEN deleted.Description LIKE '%"%'
THEN 'Changed'
ELSE
'Not Changed'
END AS ChangedStatus
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply