February 21, 2017 at 10:26 am
Hi Everyone,
I am looking to convert numbers in a delimited string to corresponding letter.
For example, if the string is '2,3,4' I want to convert it to 'B,C,D'
Important to note that their can be both semi-colon and comma delimiters, and I do want to preserve the delimiters.
Any help would be much appreciated!
-- sample data
DECLARE @Keys TABLE
(
ID INT IDENTITY(1,1)
,[Keys] VARCHAR(50)
)
INSERT INTO @Keys([Keys])
SELECT '5;7,3'
UNION ALL SELECT '15'
UNION ALL SELECT '7;8'
UNION ALL SELECT '4;6'
UNION ALL SELECT '4;7'
UNION ALL SELECT '1;2;3'
UNION ALL SELECT '2,3,4'
UNION ALL SELECT '2;4'
UNION ALL SELECT '2;3'
UNION ALL SELECT '3;2'
UNION ALL SELECT '26'
UNION ALL SELECT '2'
UNION ALL SELECT '6'
February 21, 2017 at 11:07 am
Here's an option on how to do it.
You might want to check the following articles to understand what's happening in the code and to get the function used.
http://www.sqlservercentral.com/articles/String+Manipulation/94365/
http://www.sqlservercentral.com/articles/comma+separated+list/71700/
SELECT k.ID,
k.Keys,
( SELECT CASE WHEN s.Matched = 1 THEN CHAR( s.Item + 64) ELSE s.Item END
FROM @Keys i
CROSS APPLY dbo.PatternSplitCM(i.Keys, '%[0-9]%') s
WHERE k.ID = i.ID
ORDER BY s.ItemNumber
FOR XML PATH(''), TYPE).value('./text()[1]', 'varchar(max)') AS NewKeys
FROM @Keys k
GROUP BY k.ID,
k.Keys;
February 21, 2017 at 12:14 pm
You might be able to do something simpler, depending on your specific data and requirements:
SELECT
[Keys],
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE([Keys],
'26', 'Z'), '25', 'Y'), '24', 'X'), '23', 'W'), '22', 'V'),
'21', 'U'), '20', 'T'), '19', 'S'), '18', 'R'), '17', 'Q'),
'16', 'P'), '15', 'O'), '14', 'N'), '13', 'M'), '12', 'L'),
'11', 'K'), '10', 'J'), '9', 'I'), '8', 'H'), '7', 'G'),
'6', 'F'), '5', 'E'), '4', 'D'), '3', 'C'), '2', 'B'),
'1', 'A')
FROM @Keys
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 21, 2017 at 12:31 pm
Base 10 consists of numbers 0 through 9. How do you want to handle tens, hundreds, etc. What are you trying to accomplish?
February 21, 2017 at 12:36 pm
@ScottPletcher I am trying to avoid 26 REPLACE. That is what I have currently.
@Bill Talada I don't think there would ever be a number greater than 26. If there was, it would probably be "AA"
February 21, 2017 at 12:43 pm
Why avoid 26 REPLACEs? They perform pretty well; I guess unless maybe you have a huge number of rows?!
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 21, 2017 at 12:47 pm
You still need to represent zero. Using A-J as 0-9, you could represent 10 as BA and 11 as BB. That would drastically shorten your REPLACE list and thus your CPU time.
February 21, 2017 at 1:05 pm
@ScottPletcher Yes, lots of data rows. REPLACE performs OK, but I thought I could find a better way.
@Bill Talada There will never be zero. Numbers start at 1 and go through 26. I can't change the data I am being given.
Update: We can use CHAR(64 + Number) to get the number value. So value 27 would be [
February 21, 2017 at 1:48 pm
Another option for you:
Create a custom function to do both the split and the translation at the same time and add a quick query around it. Here's the custom function, based on the LEAD version of Jeff Moden's DelimitedSplit8K string splitter:
CREATE FUNCTION dbo.DelimitedSplit8K_LEAD_CS_TRAN (
--===== Define I/O parameters
@pString VARCHAR(8000),
@pDelimiter1 CHAR(1) = ',',
@pDelimiter2 CHAR(1) = ';'
)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
--===== "Inline" CTE Driven "Tally Table” produces values from 0 up to 10,000...
-- enough to cover VARCHAR(8000)
WITH ALPHA AS (
-- Create the value list in table form to translate directly in the function
SELECT KEY_VALUE, THE_CHAR
FROM (
VALUES ('1', 'A'),
('2', 'B'),
('3', 'C'),
('4', 'D'),
('5', 'E'),
('6', 'F'),
('7', 'G'),
('8', 'H'),
('9', 'I'),
('10', 'J'),
('11', 'K'),
('12', 'L'),
('13', 'M'),
('14', 'N'),
('15', 'O'),
('16', 'P'),
('17', 'Q'),
('18', 'R'),
('19', 'S'),
('20', 'T'),
('21', 'U'),
('22', 'V'),
('23', 'W'),
('24', 'X'),
('25', 'Y'),
('26', 'Z')
) AS X (KEY_VALUE, THE_CHAR)
),
E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), --10E+1 or 10 rows
E2(N) AS (
SELECT 1
FROM E1 a, E1 b
), --10E+2 or 100 rows
E4(N) AS (
SELECT 1
FROM E2 a, E2 b
), --10E+4 or 10,000 rows max
cteTally AS (
--==== This provides the "zero base" and limits the number of rows right up front
-- for both a performance gain and prevention of accidental "overruns"
SELECT 0 AS N
UNION ALL
SELECT TOP (DATALENGTH(ISNULL(@pString,1))) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM E4
),
cteStart AS (
--==== This returns N+1 (starting position of each "element" just once for each delimiter)
SELECT t.N+1 AS N1, D.Delimiter
FROM cteTally t
LEFT OUTER JOIN (SELECT @pDelimiter1 AS Delimiter UNION ALL SELECT @pDelimiter2) AS D
ON SUBSTRING(@pString,t.N,1) = D.Delimiter
WHERE D.Delimiter IS NOT NULL
OR t.N = 0
),
THE_SPLIT AS (
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY s.N1),
Item = SUBSTRING(@pString,s.N1,ISNULL(NULLIF((LEAD(s.N1,1,1) OVER (ORDER BY s.N1) - 1),0)-s.N1,8000)),
s.Delimiter
FROM cteStart s
)
SELECT S.ItemNumber, A.THE_CHAR, S.Delimiter, S.Item
FROM THE_SPLIT AS S
LEFT OUTER JOIN ALPHA AS A
ON S.Item = A.KEY_VALUE;
GO
And here's the query to get the results, based on the function:
DECLARE @Keys TABLE
(
ID INT IDENTITY(1,1)
,[Keys] VARCHAR(50)
)
INSERT INTO @Keys([Keys])
SELECT '5;7,3'
UNION ALL SELECT '15'
UNION ALL SELECT '7;8'
UNION ALL SELECT '4;6'
UNION ALL SELECT '4;7'
UNION ALL SELECT '1;2;3'
UNION ALL SELECT '2,3,4'
UNION ALL SELECT '2;4'
UNION ALL SELECT '2;3'
UNION ALL SELECT '3;2'
UNION ALL SELECT '26'
UNION ALL SELECT '2'
UNION ALL SELECT '6';
WITH ALL_DATA AS (
SELECT K.ID, K.Keys, S.Item, S.ItemNumber, S.Delimiter, S.THE_CHAR
FROM @Keys AS K
CROSS APPLY dbo.DelimitedSplit8K_LEAD_CS_TRAN (K.Keys, ',', ';') AS S
)
SELECT D.ID, D.Keys,
(
SELECT AD.THE_CHAR + LEAD(Delimiter, 1, '') OVER(PARTITION BY AD.ID ORDER BY AD.ItemNumber)
FROM ALL_DATA AS AD
WHERE AD.ID = D.ID
FOR XML PATH('')
) AS KeyString
FROM ALL_DATA AS D
GROUP BY D.ID, D.Keys
ORDER BY D.ID;
You may be able to incorporate the outer query into the function yet, so have fun with it...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply