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

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-b]'
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-b] include the text characters and the extended set [ -~] does not?

Answer

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.

---

_{Answer copied from the duplicate question on Database Administrators}