I have a table containing data like this in a historical table.
MEMBER_ID COLORS
1 1) Red 2) Blue 3) Green
I need modify or select the data so that the result-set looks like this.
MEMBER_ID COLORS
1 #1) Red #2) Blue #3) Green
In a nutshell insert the pound before the number and the closing parenthesis.
I tried using CHARINDEX but what I have tried did not work.
I'm not sure how predictable your data is, so I will offer a suggestion and you can expand it with similar PATINDEXs for any other corner cases.
My assumptions in this suggestion are that each text might start with any single digit ( e.g "1)" ) with no preceeding space and that you have only one or two digit indices (simply expand my suggestion for higher indices), and that the only numbers to convert are those which are followed by a parens.
-- first update, done only once
-- starts with 1) ...any single digit, no preceeding space, followed by parens
;WITH TMP AS (SELECT Id FROM #SRC WHERE PATINDEX('[0-9][)]%', Colors) = 1)
UPDATE MyTable SET Colors = STUFF(Colors, 1, 0, '#')
WHERE Id IN (SELECT Id FROM TMP);
-- (you could repeat the above for strings starting with a double digit, if necessary)
-- next two updates are looped until pattern is no longer found
DECLARE @affected int = 1;
WHILE @affected > 0
BEGIN
-- e.g. 1) ...any single digit not yet converted and followed by parens
;WITH TMP AS (SELECT Id FROM #SRC WHERE PATINDEX('%[^#0-9][0-9][)]%', Colors) > 1)
UPDATE MyTable SET Colors = STUFF(Colors, PATINDEX('%[^#0-9][0-9][)]%', Colors)+1, 0, '#')
WHERE Id IN (SELECT Id FROM TMP);
SET @affected = @@rowcount;
END
SET @affected = 1;
WHILE @affected > 0
BEGIN
-- e.g. 10) ...any two digits not yet converted and followed by parens
;WITH TMP AS (SELECT Id FROM #SRC WHERE PATINDEX('%[^#0-9][0-9][0-9][)]%', Colors) > 1)
UPDATE MyTable SET Colors = STUFF(Colors, PATINDEX('%[^#0-9][0-9][0-9][)]%', Colors)+1, 0, '#')
WHERE Id IN (SELECT Id FROM TMP);
SET @affected = @@rowcount;
END
So, if you started with these three rows:
Id Colors
1 1) Red 2) Blue 3) Green
2 1) Red 20) Blue 30) Green
3 1) Red 20) Blue 3) Green
4 9) Red 10) Blue No.4 11) Green
The above routine would yield:
Id Colors
1 #1) Red #2) Blue #3) Green
2 #1) Red #20) Blue #30) Green
3 #1) Red #20) Blue #3) Green
4 #9) Red #10) Blue No.4 #11) Green
You can use Replace (Colors, ' 1', ' #1') Command to convert digit 1 to #1. and must use Replace command for each digits. In other word your query must be write as a following:
SELECT Member_Id, LTRIM(REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(' '+Colors,' 1',' #1')
,' 2',' #2')
,' 3', ' #3')
,' 4',' #4')
,' 5',' #5')
,' 6',' #6')
,' 7',' #7')
,' 8',' #8')
,' 9',' #9')) AS Colors
From YourTable
Another method:
CREATE FUNCTION dbo.f(@T NVARCHAR(100))
RETURNS NVARCHAR(100)
AS BEGIN
DECLARE @R NVARCHAR(100)='',
@IsDigit BIT = 0,
@Index INT = 1
WHILE @Index<=LEN(@T)BEGIN
IF (SUBSTRING(@T,@Index,1) IN ('1','2','3','4','5','6','7','8','9') AND @IsDigit = 0) BEGIN
SET @R = @R + '#'+SUBSTRING(@T,@Index,1)
SET @IsDigit = 1
END ELSE BEGIN
SET @R = @R + SUBSTRING(@T,@Index,1)
SET @IsDigit = 0
END
SET @Index = @Index + 1
END
RETURN @R
END
Select Member_ID, dbo.f(Colors)
From YourTable
Try this using PATINDEX(), STUFF(), CTE and Recursive query (Fiddle Demo):
--//Sample data
DECLARE @T TABLE (MEMBER_ID INT, COLOR VARCHAR(100))
INSERT @T (MEMBER_ID, COLOR)
VALUES (1, '1) Red 2) Blue 3) Green'), (2, '1) Yellow 2) Black 3) Orange')
--//Replace @T with your table name
;WITH CTE AS
(
SELECT MEMBER_ID, STUFF(COLOR, PATINDEX('%[0-9][)][ ]%', COLOR), 0, '#') COLOR, 1 NUMBER
FROM @T
UNION ALL
SELECT CTE.MEMBER_ID, STUFF(CTE.COLOR, PATINDEX('%[^#][0-9][)][ ]%', CTE.COLOR) + 1, 0, '#'), NUMBER + 1
FROM CTE JOIN @T T
ON CTE.MEMBER_ID = T.MEMBER_ID
WHERE PATINDEX('%[^#][0-9][)][ ]%', CTE.COLOR) > 0
),
CTE2 AS
(
SELECT *, ROW_NUMBER() OVER (PARTITION BY MEMBER_ID ORDER BY NUMBER DESC) rn
FROM CTE
)
SELECT MEMBER_ID,COLOR FROM CTE2 WHERE RN = 1
RESULTS:
| MEMBER_ID | COLOR |
|-----------|---------------------------------|
| 1 | #1) Red #2) Blue #3) Green |
| 2 | #1) Yellow #2) Black #3) Orange |
Assuming MS SQL Server... Easiest way is: Select MEMBER_ID, REPLACE(REPLACE(REPLACE(COLORS, '1', '#1'), '2', '#2'), '3', '#3') AS COLORS
If Numbers stored in sequence [1), 2), 3)...] you can do like the following
DECLARE @Color VARCHAR(1000) = '1) Red 2) Blue 3) Green'
DECLARE @Count INT = 1
DECLARE @Total INT = LEN(@Color) - LEN(REPLACE(@Color,') ',')')) -- Get Total Colors
-- Loop
WHILE @Count <= @Total
BEGIN
-- Adding '#'
SET @Color = REPLACE(' ' + LTRIM(@Color),' ' + CAST(@Count AS VARCHAR) + ')', ' #' + CAST(@Count AS VARCHAR) + ')')
SET @Count = @Count + 1
END
And you can update it in table. You can make this as UDF.
Here's a function that works:
create function doColors(@input varchar(max))
returns varchar(max)
as
begin
declare @parenIndex int
declare @numIndex int = 1
select @parenIndex = CHARINDEX(')', @input, 0)
while @parenIndex > 0
begin
set @numIndex = 1
while isnumeric(SUBSTRING(@input, @parenIndex-@numIndex, 1)) = 1
begin
set @numindex = @numIndex + 1
end
if @numIndex > 1 and SUBSTRING(@input, @parenIndex-(@numIndex), 1) = ' '
begin
set @input = stuff(@input, @parenIndex-(@numIndex-1), 0, '#')
end
select @parenIndex = CHARINDEX(')', @input, @parenIndex+2)
end
return @input
end
It basically finds the parenthesis and then works backwards looking for numbers until it doesn't find any more, then inserts the #. It works for any number of colors and handles edge cases like numbers, parentheses, and # in the color name.
1) Red 12) 33 Orange 144) Pink 147) Purple #12 150) Turquoise (light blue) 1024) Brown
1) Mauve 2) Perrywinkle (13) 3) Black (#12)
becomes
#1) Red #12) 33 Orange #144) Pink #147) Purple #12 #150) Turquoise (light blue) #1024) Brown
#1) Mauve #2) Perrywinkle (13) #3) Black (#12)
