SQL - Loop through a string and show all the ASCII characters

This short script will show how to loop through a string in T-SQL and display the ASCII characters.

Why would you need to do this? Sometime you need to see (visibly) the placement of non printable characters in a string, like CR (ASCII 13), LF (ASCII 10) or TAB (ASCII(9). This script does that. The output will look take text that look like this:

And show this:

Related Articles

... and you 'll find more on the SQL (General) Menu

 

declare @pos int
declare @field varchar(1000)
select @field = remarks from DynColumn where TABLE_NAME = 'pa01001' 
        and COLUMN_NAME = 'patu'
set @pos = 0
declare @char varchar(1)
while @pos < LEN(@field)
begin
    set @char = SUBSTRING(@field,@pos,1)
      
    print convert(char(10),@pos) + 
        case 
            when ascii(@char) in (10) then 'LF   ' 
            when ascii(@char) in (13) then 'CR   ' 
            when ascii(@char) in (9) then 'TAB  ' 
            else @char + '    ' end  + 
        convert(char(10),ascii(@char))
    set @pos = @pos + 1 
  
end
        

 

RealWorldCode gives developers practical, real‑world solutions with clean, working code — no fluff, no theory, just answers.
Links
Home
Knowledge Areas
Sitemap
Contact
Et cetera
Privacy Policy
Terms and Conditions
Cookie Preferences