Replace all ful stops in all records with nothing
-
I would like to replace all full stops in all records of a particular field with nothing. The field is defined as varchar. I would also like to concatenate these strings with the letter A when a condition is met (where other_col = x). For example, after the transformation 176.7 would become A1767.
Please can someone help me with this.
-
Can you post some sample data and the required out. I don't have quite enough test data to go on with this!
-
-- Test Data
DECLARE @t TABLE
(
StopCol varchar(20) NOT NULL
,other_col char(1) NOT NULL
)
INSERT INTO @t
SELECT '176.7', 'y' UNION ALL
SELECT '4476.7', 'X' UNION ALL
SELECT '176', 'y' UNION ALL
SELECT '4476', 'X'
-- Show test data
SELECT *
FROM @t
UPDATE @t
SET StopCol =
CASE other_col
WHEN 'x' THEN 'A' + REPLACE(StopCol, '.', '')
ELSE REPLACE(StopCol, '.', '')
END
WHERE CHARINDEX('.', StopCol) > 0
-- Show results
SELECT *
FROM @t
-
Thanks for the test script. I don't want to remove the number after the full stop. I just want to remove the full stop. (I am pleased that you did send it to me as a test script.)
-
Ken's example does just that. Why don't you follow Remi's advice and post some sample data and your expected output?
-
Sorry, I didn't leave enough display space open on my result viewer.
-
Taking a guess without samples but you might try
something like:
UPDATE table_name
SET field_name = CASE CHARINDEX('.', CONVERT(varchar(10), field_name), 1) WHEN > 0 THEN
-- Deals with other_col value for prefix
CASE WHEN other_col = 'x' THEN 'A' + REPLACE(CONVERT(VARCHAR(10), field_name), '.', '')
-- Will change all others with '.' regardless
ELSE REPLACE(CONVERT(VARCHAR(10), field_name), '.', '')
END
END
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply