CODEWITHSUNDEEP
sql-server-2008t-sql
haku
haku

Reputation: 4505

Convert a String to an array of characters or an alternative - Tsql

Getting this done in C# would have been such an easy task, but I am required to do this in sql. Basically, I need to write a tsql function that would convert a given varchar to it's corresponding encoding. For instance, if an input is '123456789' our business rule requires it be converted to 'ABCDEFGHI.' In short, I am trying to convert below c# code to tsql: 

class Program
{
    static void Main(string[] args)
    {
        string a = "1234567890";

        char[] oldA = a.ToCharArray();
        char[] newA = new char[10];

        for (int i =0; i<oldA.Length; i++)
        {
            switch (oldA[i])
            {
                case '1':
                    newA[i] = 'A';
                    break;

                case '2':
                    newA[i] = 'B';
                    break;

                    //and so on..
            }
        }

   Console.WriteLine(newA);

   Console.ReadKey();

    }
}

I am confirmed that there is no array like thing in tsql, and most of the answers I came across assumed that it had some kind of delimiter to it, so wasn't particularly very useful.

Thanks.

Upvotes: 0

Views: 5076

Answers (3)

Moe Sisko
Moe Sisko

Reputation: 12015

Based on peterm's second option (if it is safe to assume that all chars in the input will be between '0' and 9'):

CREATE FUNCTION myfunc2(@expr AS VARCHAR(255)) RETURNS VARCHAR(255)
BEGIN
  DECLARE @i AS INT = 1, @chr CHAR(1), @result VARCHAR(255) = ''
  WHILE @i <= LEN(@expr)
  BEGIN
    SET @chr = SUBSTRING(@expr, @i, 1)
    SET @result = @result + 
      CASE @chr
        WHEN '0' THEN 'J'
        ELSE CHAR(64 + cast(@chr as int))
      END
      SET @i = @i + 1
  END -- WHILE
  RETURN @result
END

Or, possibly more efficient, since reduces the number of CAST calls:

CREATE FUNCTION myfunc2(@expr AS VARCHAR(255)) RETURNS VARCHAR(255)
BEGIN
  DECLARE @i AS INT = 1, @b1 BINARY(1), @result VARCHAR(255) = ''
  declare @b VARBINARY(255)
  SET @b = CAST(@expr as VARBINARY(255))

  WHILE @i <= LEN(@b)
  BEGIN
    SET @b1 = SUBSTRING(@b, @i, 1)
    SET @result = @result + 
      CASE @b1
        WHEN 48 THEN 'J'   -- 48 is ascii '0'
        ELSE CHAR(16 + @b1)  -- to get from '1' to 'A' on ASCII chart, need to add 16. 
      END
    SET @i = @i + 1
  END -- WHILE
  RETURN @result
END

Upvotes: 0

peterm
peterm

Reputation: 92795

You can do either

CREATE FUNCTION myfunc(@expr AS VARCHAR(255)) RETURNS VARCHAR(255)
BEGIN
RETURN REPLACE(
  REPLACE(
    REPLACE(
      REPLACE(
        REPLACE(
          REPLACE(
            REPLACE(
              REPLACE(
                REPLACE(
                  REPLACE(@expr, '1', 'A'),
                '2', 'B')
              '3', 'C'),
            '4', 'D'),
          '5','E'),
        '6', 'F'),
      '7', 'G'),
    '8', 'H'),
  '9', 'I'),
'0','J')
END

or (which is much closer to what you've got in C#)

CREATE FUNCTION myfunc2(@expr AS VARCHAR(255)) RETURNS VARCHAR(255)
BEGIN
  DECLARE @i AS INT = 1, @chr CHAR(1), @result VARCHAR(255) = ''
  WHILE @i <= LEN(@expr)
  BEGIN
    SET @chr = SUBSTRING(@expr, @i, 1)
    SET @result = @result + 
      CASE @chr
        WHEN '1' THEN 'A'
        WHEN '2' THEN 'B'
        WHEN '3' THEN 'C'
        WHEN '4' THEN 'D'
        WHEN '5' THEN 'E'
        WHEN '6' THEN 'F'
        WHEN '7' THEN 'G'
        WHEN '8' THEN 'H'
        WHEN '9' THEN 'I'
        WHEN '0' THEN 'J'
        ELSE @chr
      END
      SET @i = @i + 1
  END -- WHILE
  RETURN @result
END

Sample usage for both functions:

SELECT dbo.myfunc(value) result, dbo.myfunc2(value) result2
  FROM
(
  VALUEs ('1234567890'), ('5512314567')
) t(value);

Sample output:

|     RESULT |    RESULT2 |
|------------|------------|
| ABCDEFGHIJ | ABCDEFGHIJ |
| EEABCADEFG | EEABCADEFG |

Here is a SQLFiddle demo

Upvotes: 3

Nick.Mc
Nick.Mc

Reputation: 19194

Here is a function that I put in most databases that I work with:

CREATE FUNCTION [dbo].[f_ForLoop](@Start INT , @End INT, @Increment INT = 1)
RETURNS @Loop TABLE 
(
RowNumber INT
)
AS
--=============================================================
-- f_ForLoop
-- 
-- DATE         AUTHOR          DESCRIPTION
-- 17 Apr 2012  Nick McDermaid  Created
--
-- PURPOSE:
-- Return a table that can be joined to with the number of records indicated in parameters
--
-- USAGE:
-- SELECT RowNumber FROM dbo.f_ForLoop(1,10,1)
-- SELECT RowNumber FROM dbo.f_ForLoop(10,1,-1)
-- SELECT RowNumber FROM dbo.f_ForLoop(2,20,2)
--=============================================================
BEGIN
IF @Increment = 0 RETURN
IF @Increment < 0 AND @Start < @End RETURN
IF @Increment > 0 AND @Start > @End RETURN
IF @Increment IS NULL SET @Increment = 1

WHILE NOT (@Start = @End)
BEGIN
    INSERT INTO @Loop (RowNumber) VALUES (@Start)
    SET @Start = @Start + @Increment
END

INSERT INTO @Loop (RowNumber) VALUES (@Start)
RETURN
END

Then you can use it something like this:

DECLARE @YourString VARCHAR(50)

SET @YourString='ABCDEFGHIJ'

SELECT 
RowNumber, 
SUBSTRING(@YourString,RowNumber,1) As Token, 
ASCII(SUBSTRING(@YourString,RowNumber,1)) - 64 Result
FROM dbo.f_ForLoop(1,LEN(@YourString),1)

This is not exactly what you're after but serves to highlight some functionality.

The next step is to join to a mapping table instead of using ASCII because clearly this is not what you're after. This of course assume that one character is one token.

Upvotes: 0

Related Questions