Convert column value to Ascii using division and mod in sQL SERVER

Question

I have a table in SQL SERVER as below with many columns like below

| D401                         | D402                         |
|------------------------------|------------------------------|
| 20808 8257 12339 12848 12345 | 20808 8257 1233 12848 12345  |
| 20808 8262 12849 14640 12852 | 20808 8262 12849 14640 12852 |

Requirement: Convert value ascii

Example in python

1. Split D401 column value

df['D401'].str.split(pat="\t", expand=True).replace(r'\s+|\\n', ' ',regex=True).apply(pd.to_numeric)

2. Convert convert to ascii by mod

• divide each split column by 256 and mod by 256

        # Apply mod function to each split column
        df['v0'] = df['v0'].apply(lambda x: chr(round(x / 256)) + chr(x % 256)).apply(lambda x: x[::-1])
        df['v1'] = df['v1'].apply(lambda x: chr(round(x / 256)) + chr(x % 256)).apply(lambda x: x[::-1])
        df['v2'] = df['v2'].apply(lambda x: chr(round(x / 256)) + chr(x % 256)).apply(lambda x: x[::-1])
        df['v3'] = df['v3'].apply(lambda x: chr(round(x / 256)) + chr(x % 256)).apply(lambda x: x[::-1])
        df['v4'] = df['v4'].apply(lambda x: chr(round(x / 256)) + chr(x % 256)).apply(lambda x: x[::-1])
     # after mod, join column

        df[D401] = df['v0'].astype(str) + df['v1'].astype(str) \
                + df['v2'].astype(str) + df['v3'].astype(str) \
                + df['v4'].astype(str)
     # drop old unnecessary split column
        df.drop(['v0', 'v1', 'v2', 'v3', 'v4'], axis=1, inplace=True)

Ascii Output

| D401       | D402       |
|------------|------------|
| HQA 300290 | HQA 300290 |
| HQF 120942 | HQF 120942 |

Is there a way to do the same in SQL SERVER itself

When i copy paste the data in notepad from sql table, it has 36 space at last

'20808 8257 12339 13616 13875                                    '

When i read it in Python it shows as below

'20808\t8257\t12339\t13616\t13875\r\n'

Answer 1

As you have stated: use division and modulus to perform the conversion. After that concatenate into one string.

The solution below split the string into row wise and then use string_agg() to concatenate it

-- create sample table
create table tbl
(
    col varchar(100)
)
    
-- insert sample data
insert into tbl(col) values 
('20808 8257 12339 12848 12345'),
('20808 8262 12849 14640 12852');

-- change the space to tab delimiter
update tbl
set    col = replace(col, ' ', char(9));

-- define the delimiter : tab
declare @d char(1) = char(9);

-- using recursive cte to split the string on space
with cte as
(
     select org = col,
            n = 1,
            v = left(col, p - 1),
            col = substring(col, p + 1, len(col) - p.p)
     from   (
                select col = col + @d
                from   tbl 
            ) t
            cross apply
            (
                select p = case when charindex(@d, col) <> 0
                                then charindex(@d, col)
                                else len(col)
                                end
            ) p
            
     union all
            
     select org = org,
            n = n + 1,
            v = left(col, p.p - 1),
            col = substring(col, p.p + 1, len(col) - p.p)
     from   cte t
            cross apply
            (
                select p = case when charindex(@d, col) <> 0
                                then charindex(@d, col)
                                else len(col)
                                end
            ) p          
      where t.col <> ''
)
select org,
       string_agg(char(v % 256) + char(v / 256), '') within group (order by n)
from   cte
group by org

db<>fiddle demo

db<>fiddel demo for 2 columns

Updates: Updated both fiddle to use tab delimiter

I have added another fiddle which uses Jeff Moden's DelimitedSplit8K. DelimitedSplit8K returns a ItemNumber. Unless you are working on Azure, the normal string_split() does not return an ordinal column which you required for the string_agg().

db<>fiddle demo using DelimitedSplit8K