Convert number to certain value from table

• BTG

  SSC Veteran

  Points: 250

  More actions

  January 26, 2017 at 12:08 am

  #318181

  Hi, I have table name [@tbl_value], columns are 'Code', 'Name'
  Records:
  Code Name
  1       B
  2       A
  3       D
  4       C

  When user have the value of 341. The result would be. DCA. Another example is: 3321 the result would be: DDAB.

• Eirikur Eiriksson

  SSC Guru

  Points: 182882

  More actions

  January 26, 2017 at 1:24 am

  #1925008

  Quick suggestion (if I understood the requirements correctly)
  😎

  USE TEEST;
GO
SET NOCOUNT ON;

  DECLARE @tbl_value TABLE (Code INT NOT NULL,Name VARCHAR(10) NOT NULL);
INSERT INTO @tbl_value(Code,Name)
VALUES (1,'A')
  ,(2,'B')
  ,(3,'C')
  ,(4,'D')
;
DECLARE @USER_VAL INT = 321;

  ;WITH SINGLE_DIGITS(POS,DX) AS
(
  SELECT 1, @USER_VAL     % 10 UNION ALL
  SELECT 2, @USER_VAL / 10  % 10 UNION ALL
  SELECT 3, @USER_VAL / 100  % 10 UNION ALL
  SELECT 4, @USER_VAL / 1000 % 10 UNION ALL
  SELECT 5, @USER_VAL / 10000 % 10 UNION ALL
  SELECT 6, @USER_VAL / 100000 % 10
)
SELECT
(
  SELECT
   '' + TV.Name
  FROM   @tbl_value    TV
  INNER JOIN SINGLE_DIGITS   SD
  ON    SD.DX    = TV.Code
  ORDER BY  SD.POS DESC
  FOR XML PATH(''),TYPE
).value('(./text())[1]','VARCHAR(10)') AS NAME_STR;


  Output
  NAME_STR
----------
CBA


• BTG

  SSC Veteran

  Points: 250

  More actions

  January 26, 2017 at 1:31 am

  #1925009

  Eirikur Eiriksson - Thursday, January 26, 2017 1:24 AM

  Quick suggestion (if I understood the requirements correctly)
  😎

  USE TEEST;
GO
SET NOCOUNT ON;

  DECLARE @tbl_value TABLE (Code INT NOT NULL,Name VARCHAR(10) NOT NULL);
INSERT INTO @tbl_value(Code,Name)
VALUES (1,'A')
  ,(2,'B')
  ,(3,'C')
  ,(4,'D')
;
DECLARE @USER_VAL INT = 321;

  ;WITH SINGLE_DIGITS(POS,DX) AS
(
  SELECT 1, @USER_VAL     % 10 UNION ALL
  SELECT 2, @USER_VAL / 10  % 10 UNION ALL
  SELECT 3, @USER_VAL / 100  % 10 UNION ALL
  SELECT 4, @USER_VAL / 1000 % 10 UNION ALL
  SELECT 5, @USER_VAL / 10000 % 10 UNION ALL
  SELECT 6, @USER_VAL / 100000 % 10
)
SELECT
(
  SELECT
   '' + TV.Name
  FROM   @tbl_value    TV
  INNER JOIN SINGLE_DIGITS   SD
  ON    SD.DX    = TV.Code
  ORDER BY  SD.POS DESC
  FOR XML PATH(''),TYPE
).value('(./text())[1]','VARCHAR(10)') AS NAME_STR;


  Output
  NAME_STR
----------
CBA


  Hi Eirikur,

  Thank you for the suggestion. For the,

  SELECT 1, @USER_VAL  % 10 UNION ALL
SELECT 2, @USER_VAL / 10 % 10 UNION ALL
SELECT 3, @USER_VAL / 100 % 10 UNION ALL
SELECT 4, @USER_VAL / 1000 % 10 UNION ALL
SELECT 5, @USER_VAL / 10000 % 10 UNION ALL
SELECT 6, @USER_VAL / 100000 % 10

  How can I add the 0? 

• Eirikur Eiriksson

  SSC Guru

  Points: 182882

  More actions

  January 26, 2017 at 1:56 am

  #1925013

  Here is a version with 0
  😎

  USE TEEST;
GO
SET NOCOUNT ON;

  DECLARE @tbl_value TABLE (Code INT NOT NULL,Name VARCHAR(10) NOT NULL);
INSERT INTO @tbl_value(Code,Name)
VALUES (1,'A')
  ,(2,'B')
  ,(3,'C')
  ,(4,'D')
  ,(0,'X')
;
DECLARE @USER_VAL INT = 33410;

  ;WITH SINGLE_DIGITS(POS,DX) AS
(
  SELECT 1, @USER_VAL     % 10 UNION ALL
  SELECT 2, @USER_VAL / 10  % 10 UNION ALL
  SELECT 3, @USER_VAL / 100  % 10 UNION ALL
  SELECT 4, @USER_VAL / 1000 % 10 UNION ALL
  SELECT 5, @USER_VAL / 10000 % 10 UNION ALL
  SELECT 6, @USER_VAL / 100000 % 10
)
SELECT
(
  SELECT
   '' + TV.Name
  FROM   @tbl_value    TV
  INNER JOIN SINGLE_DIGITS   SD
  ON    SD.DX    = TV.Code
  WHERE   SD.POS <= (CEILING(LOG10(@USER_VAL + 0.0)))
  ORDER BY  SD.POS DESC
  FOR XML PATH(''),TYPE
).value('(./text())[1]','VARCHAR(10)') AS NAME_STR;


  Output
  
NAME_STR
----------
CCDAX

• sajikv2007

  SSC Veteran

  Points: 257

  More actions

  January 26, 2017 at 9:28 am

  #1925095

  Check this logic. 
  DECLARE @tbl_value TABLE (Code INT NOT NULL,Name VARCHAR(10) NOT NULL);
  INSERT INTO @tbl_value(Code,Name)
  VALUES (1,'A')
  ,(2,'B')
  ,(3,'C')
  ,(4,'D')

  DECLARE @User_val INT = 12412
  DECLARE @Result VARCHAR(1000) = '', @char CHAR(1) = ''
  While(LEN(@User_val) >=1)
  BEGIN
  SELECT @char = LEFT(@User_val,1)
  SELECT @Result = @Result + Name FROM @tbl_value WHERE Code = @char
  SELECT @User_val = RIGHT(@User_val,LEN(@User_val)-1)
  IF(@User_val=0)
  BREAK;
  END
  SELECT @Result

• sajikv2007

  SSC Veteran

  Points: 257

  More actions

  January 26, 2017 at 9:30 am

  #1925098

  sajikv2007 - Thursday, January 26, 2017 9:28 AM

  Check this logic. 
  DECLARE @tbl_value TABLE (Code INT NOT NULL,Name VARCHAR(10) NOT NULL);
  INSERT INTO @tbl_value(Code,Name)
  VALUES (1,'A')
  ,(2,'B')
  ,(3,'C')
  ,(4,'D')

  DECLARE @User_val INT = 12412
  DECLARE @Result VARCHAR(1000) = '', @char CHAR(1) = ''
  While(LEN(@User_val) >=1)
  BEGIN
  SELECT @char = LEFT(@User_val,1)
  SELECT @Result = @Result + Name FROM @tbl_value WHERE Code = @char
  SELECT @User_val = RIGHT(@User_val,LEN(@User_val)-1)
  IF(@User_val=0)
  BREAK;
  END
  SELECT @Result

  Check this logic. 
  DECLARE @tbl_value TABLE (Code INT NOT NULL,Name VARCHAR(10) NOT NULL);
  INSERT INTO @tbl_value(Code,Name)
  VALUES (1,'A')
  ,(2,'B')
  ,(3,'C')
  ,(4,'D')

  DECLARE @User_val INT = 12412 -- This is the user input value.
  DECLARE @Result VARCHAR(1000) = '', @char CHAR(1) = ''
  While(LEN(@User_val) >=1)
  BEGIN
  SELECT @char = LEFT(@User_val,1)
  SELECT @Result = @Result + Name FROM @tbl_value WHERE Code = @char
  SELECT @User_val = RIGHT(@User_val,LEN(@User_val)-1)
  IF(@User_val=0)
  BREAK;
  END
  SELECT @Result

• Joe Torre

  SSChampion

  Points: 10248

  More actions

  January 26, 2017 at 11:48 am

  #1925144

  bryan_gomez032000 - Thursday, January 26, 2017 12:08 AM

  Hi, I have table name [@tbl_value], columns are 'Code', 'Name'
  Records:
  Code Name
  1       B
  2       A
  3       D
  4       C

  When user have the value of 341. The result would be. DCA. Another example is: 3321 the result would be: DDAB.

  Try:
  

  DECLARE @Num int=3412, @String varchar(6)='';

  WITH String(pos, chr)

  AS ( SELECT sv.number, Substring(Cast(@Num AS varchar(6)), sv.number, 1)

  FROM master.dbo.spt_values sv

  WHERE sv.type = 'P'

  AND sv.number BETWEEN 1 AND Len(Cast(@Num AS varchar(6)))

  )

  SELECT @String+=Char(chr+64)

  FROM String ;

  SELECT @String String;

• Eirikur Eiriksson

  SSC Guru

  Points: 182882

  More actions

  January 26, 2017 at 12:04 pm

  #1925151

  sajikv2007 - Thursday, January 26, 2017 9:30 AM

  sajikv2007 - Thursday, January 26, 2017 9:28 AM

  Check this logic. 
  DECLARE @tbl_value TABLE (Code INT NOT NULL,Name VARCHAR(10) NOT NULL);
  INSERT INTO @tbl_value(Code,Name)
  VALUES (1,'A')
  ,(2,'B')
  ,(3,'C')
  ,(4,'D')

  DECLARE @User_val INT = 12412
  DECLARE @Result VARCHAR(1000) = '', @char CHAR(1) = ''
  While(LEN(@User_val) >=1)
  BEGIN
  SELECT @char = LEFT(@User_val,1)
  SELECT @Result = @Result + Name FROM @tbl_value WHERE Code = @char
  SELECT @User_val = RIGHT(@User_val,LEN(@User_val)-1)
  IF(@User_val=0)
  BREAK;
  END
  SELECT @Result

  Check this logic. 
  DECLARE @tbl_value TABLE (Code INT NOT NULL,Name VARCHAR(10) NOT NULL);
  INSERT INTO @tbl_value(Code,Name)
  VALUES (1,'A')
  ,(2,'B')
  ,(3,'C')
  ,(4,'D')

  DECLARE @User_val INT = 12412 -- This is the user input value.
  DECLARE @Result VARCHAR(1000) = '', @char CHAR(1) = ''
  While(LEN(@User_val) >=1)
  BEGIN
  SELECT @char = LEFT(@User_val,1)
  SELECT @Result = @Result + Name FROM @tbl_value WHERE Code = @char
  SELECT @User_val = RIGHT(@User_val,LEN(@User_val)-1)
  IF(@User_val=0)
  BREAK;
  END
  SELECT @Result

  Cannot recommend this kind of concatenation, no way af guaranteeing the order
  😎
  SELECT @Result = @Result + Name FROM @tbl_value WHERE Code = @char


  Further, this logic is not inline-able hence it forces one to use a scalar function or a stored procedure, which have considerably higher execution overhead.

• Eirikur Eiriksson

  SSC Guru

  Points: 182882

  More actions

  January 26, 2017 at 12:05 pm

  #1925152

  Joe Torre - Thursday, January 26, 2017 11:48 AM

  bryan_gomez032000 - Thursday, January 26, 2017 12:08 AM

  Hi, I have table name [@tbl_value], columns are 'Code', 'Name'
  Records:
  Code Name
  1       B
  2       A
  3       D
  4       C

  When user have the value of 341. The result would be. DCA. Another example is: 3321 the result would be: DDAB.

  Try:
  

  DECLARE @Num int=3412, @String varchar(6)='';

  WITH String(pos, chr)

  AS ( SELECT sv.number, Substring(Cast(@Num AS varchar(6)), sv.number, 1)

  FROM master.dbo.spt_values sv

  WHERE sv.type = 'P'

  AND sv.number BETWEEN 1 AND Len(Cast(@Num AS varchar(6)))

  )

  SELECT @String+=Char(chr+64)

  FROM String ;

  SELECT @String String;

  What if the Name becomes a word, not a character?
  😎

• ScottPletcher

  SSC Guru

  Points: 100949

  More actions

  January 26, 2017 at 12:10 pm

  #1925154

  I wouldn't limit the values to single chars or require each value to be the next letter in the alphabet.  Instead, for example:

  INSERT INTO #tbl_value(Code,Name)
VALUES(5,'QQQ');

  ;WITH
cteTally10 AS (
  SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
),
cteTally100 AS (
  SELECT ROW_NUMBER() OVER(ORDER BY c1.number) AS number
  FROM cteTally10 c1
  CROSS JOIN cteTally10 c2
)
SELECT user_id, value, value_new
FROM (
  VALUES(1, '341'),(2, '135')
) AS td(user_id, value) /*td=test_data*/
OUTER APPLY (
  SELECT
   '' + tv.Name
  FROM cteTally100 t
  INNER JOIN #tbl_value tv ON tv.Code = SUBSTRING(td.value, t.number, 1)
  WHERE t.number BETWEEN 1 AND LEN(td.value)  
  ORDER BY t.number
  FOR XML PATH('')
) AS oa1(value_new)

  SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

• Joe Torre

  SSChampion

  Points: 10248

  More actions

  January 26, 2017 at 12:11 pm

  #1925155

  Eirikur Eiriksson - Thursday, January 26, 2017 12:05 PM

  Joe Torre - Thursday, January 26, 2017 11:48 AM

  bryan_gomez032000 - Thursday, January 26, 2017 12:08 AM

  Hi, I have table name [@tbl_value], columns are 'Code', 'Name'
  Records:
  Code Name
  1       B
  2       A
  3       D
  4       C

  When user have the value of 341. The result would be. DCA. Another example is: 3321 the result would be: DDAB.

  Try:
  

  DECLARE @Num int=3412, @String varchar(6)='';

  WITH String(pos, chr)

  AS ( SELECT sv.number, Substring(Cast(@Num AS varchar(6)), sv.number, 1)

  FROM master.dbo.spt_values sv

  WHERE sv.type = 'P'

  AND sv.number BETWEEN 1 AND Len(Cast(@Num AS varchar(6)))

  )

  SELECT @String+=Char(chr+64)

  FROM String ;

  SELECT @String String;

  What if the Name becomes a word, not a character?
  😎

  Scope creep

• Eirikur Eiriksson

  SSC Guru

  Points: 182882

  More actions

  January 26, 2017 at 12:21 pm

  #1925162

  Joe Torre - Thursday, January 26, 2017 12:11 PM

  Eirikur Eiriksson - Thursday, January 26, 2017 12:05 PM

  Joe Torre - Thursday, January 26, 2017 11:48 AM

  bryan_gomez032000 - Thursday, January 26, 2017 12:08 AM

  Hi, I have table name [@tbl_value], columns are 'Code', 'Name'
  Records:
  Code Name
  1       B
  2       A
  3       D
  4       C

  When user have the value of 341. The result would be. DCA. Another example is: 3321 the result would be: DDAB.

  Try:
  

  DECLARE @Num int=3412, @String varchar(6)='';

  WITH String(pos, chr)

  AS ( SELECT sv.number, Substring(Cast(@Num AS varchar(6)), sv.number, 1)

  FROM master.dbo.spt_values sv

  WHERE sv.type = 'P'

  AND sv.number BETWEEN 1 AND Len(Cast(@Num AS varchar(6)))

  )

  SELECT @String+=Char(chr+64)

  FROM String ;

  SELECT @String String;

  What if the Name becomes a word, not a character?
  😎

  Scope creep

  Looks more like an assumption to me, although the ordinal position of the characters is the alphabetical order, it does not mean that this is real data.
  😎

• ScottPletcher

  SSC Guru

  Points: 100949

  More actions

  January 26, 2017 at 2:27 pm

  #1925200

  Eirikur Eiriksson - Thursday, January 26, 2017 12:21 PM

  Joe Torre - Thursday, January 26, 2017 12:11 PM

  Eirikur Eiriksson - Thursday, January 26, 2017 12:05 PM

  Joe Torre - Thursday, January 26, 2017 11:48 AM

  bryan_gomez032000 - Thursday, January 26, 2017 12:08 AM

  Hi, I have table name [@tbl_value], columns are 'Code', 'Name'
  Records:
  Code Name
  1       B
  2       A
  3       D
  4       C

  When user have the value of 341. The result would be. DCA. Another example is: 3321 the result would be: DDAB.

  Try:
  

  DECLARE @Num int=3412, @String varchar(6)='';

  WITH String(pos, chr)

  AS ( SELECT sv.number, Substring(Cast(@Num AS varchar(6)), sv.number, 1)

  FROM master.dbo.spt_values sv

  WHERE sv.type = 'P'

  AND sv.number BETWEEN 1 AND Len(Cast(@Num AS varchar(6)))

  )

  SELECT @String+=Char(chr+64)

  FROM String ;

  SELECT @String String;

  What if the Name becomes a word, not a character?
  😎

  Scope creep

  Looks more like an assumption to me, although the ordinal position of the characters is the alphabetical order, it does not mean that this is real data.
  😎

  Besides which, if you look closely, you'll see the data in the original q is:
  1 B
  2 A
  3 D
  4 C
  i.e., not 1A2B3C4D.

  SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

• BTG

  SSC Veteran

  Points: 250

  More actions

  February 2, 2017 at 2:36 am

  #1926270

  Sorry for late response. I've managed to solved my concern. Thanks to all of you.

  ScottPletcher - Thursday, January 26, 2017 12:10 PM

  I wouldn't limit the values to single chars or require each value to be the next letter in the alphabet.  Instead, for example:

  INSERT INTO #tbl_value(Code,Name)
VALUES(5,'QQQ');

  ;WITH
cteTally10 AS (
  SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
),
cteTally100 AS (
  SELECT ROW_NUMBER() OVER(ORDER BY c1.number) AS number
  FROM cteTally10 c1
  CROSS JOIN cteTally10 c2
)
SELECT user_id, value, value_new
FROM (
  VALUES(1, '341'),(2, '135')
) AS td(user_id, value) /*td=test_data*/
OUTER APPLY (
  SELECT
   '' + tv.Name
  FROM cteTally100 t
  INNER JOIN #tbl_value tv ON tv.Code = SUBSTRING(td.value, t.number, 1)
  WHERE t.number BETWEEN 1 AND LEN(td.value)  
  ORDER BY t.number
  FOR XML PATH('')
) AS oa1(value_new)