0

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?

3

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.

2
  • Sorry, that was a typo. I meant [a-z]. – manymanymore Mar 26 at 10:43
  • @manymanymore I thought it might be, but it was repeated several times in the question, so I assumed not, and you were using some sort of weird collation where that might work. You missed one in the question edit, so I fixed that. – Paul White Mar 26 at 10:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.