Show the ASCII code of each character in a string-CodePudding

In T-SQL, how to show the ASCII code of each character in a string?

Example of a string:

Adrs_line1
ABCD

I would like to show this:

Adrs_line1_ASCII
65|66|67|68

Currently, I do this (I increment the number manually):

ASCII(SUBSTRING(Adrs_line1, 1, 1))

The all purpose of this is to find which character break an address. Because this code doesn't work for 5% of the addresses :

PARSENAME(REPLACE(REPLACE(ADDRESSSTREET, CHAR(13), ''), CHAR(10), '.'), 3) as Adrs_line1,
PARSENAME(REPLACE(REPLACE(ADDRESSSTREET, CHAR(13), ''), CHAR(10), '.'), 2) as Adrs_line2,
PARSENAME(REPLACE(REPLACE(ADDRESSSTREET, CHAR(13), ''), CHAR(10), '.'), 1) as Adrs_line3,

EDIT :

More information about my problem :

In our ERP, the address is shown with return carriage (line 1, line 2 and line 3). But in the database, the three lines are concatened in one string. So, I have to seperate the three lines by detecting the character that do the return carriage. Normally is char(10) or char(13), but for 5% of the adresses, it's another character that I can't find manually.

CodePudding user response：

Here's something you might be able to use and adapt.

Create a table-valued function that will return any deemed "bad" characters:

create or alter function dbo.FindBadAscii (@v varchar(max))
returns table as
return
  select v [Char], Ascii(v) [Ascii] 
  from (
    select top (Len(@v)) Row_Number() over(order by (select null)) n 
    from master.dbo.spt_values 
  ) d
  cross apply(values(Substring(@v, d.n, 1)))x(v)
  where Ascii(v)<32 and Ascii(v) not in (9,10,13)

You can then test it like so:

select * from dbo.FindBadAscii ('a'   Char(10)   'b'   Char(20)   'c')

This identifies any "control" characters that are not a tab/lf/cr

Result

20