How can I output text until it is ascii in SQL?

All we need is an easy explanation of the problem, so here it is.

How can I output text until it is ascii in SQL?

Here is what I am trying to do:

DECLARE @input VARCHAR(20)
SET @input = 'text'

DECLARE @index INT
SET @index = 1

DECLARE @output VARCHAR(32)
SET @output = ''

WHILE CHAR(ASCII(SUBSTRING(@input, @index, 1))) LIKE '[ -~]'
BEGIN
    SET @output = @output + CHAR(ASCII(SUBSTRING(@input, @index, 1)))
    SET @index = @index + 1
END

SELECT @output

But in the end I am getting an empty string. Why? What am I missing here?

I am expecting the value of the @output to be 'text' in the end of the script.

UPDATE

If I update the script to the

DECLARE @input VARCHAR(20)
SET @input = 'text'

DECLARE @index INT
SET @index = 1

DECLARE @output VARCHAR(32)
SET @output = ''

WHILE CHAR(ASCII(SUBSTRING(@input, @index, 1))) LIKE '[a-z]'
BEGIN
    SET @output = @output + CHAR(ASCII(SUBSTRING(@input, @index, 1)))
    SET @index = @index + 1
END

SELECT @output

It will work as expected. But here I just shrinked the set from all printable ascii characters to only small letters. Why does the shrinked set [a-z] include the text characters and the extended set [ -~] does not?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

A space is not a valid range delimiter so LIKE [ -~] will not work. That becomes a test for those three characters only.

You could check the ASCII code directly, rather than using LIKE:

DECLARE @input VARCHAR(20)
SET @input = 'text'

DECLARE @index INT
SET @index = 1

DECLARE @output VARCHAR(32)
SET @output = ''

WHILE ASCII(SUBSTRING(@input, @index, 1)) BETWEEN 32 AND 126
BEGIN
    SET @output = @output + CHAR(ASCII(SUBSTRING(@input, @index, 1)))
    SET @index = @index + 1
END

SELECT @output

demo

Side note: It’s possible to get unexpected results with LIKE ranges because the comparison is collation-aware.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Tags: ,

Leave a Reply