Hi all,

Three related questions.

Q1)
I tried the following code and it works with

set @c = CHAR(@x + ASCII('a')) + '-' + @c

but it does not work with

set @c = @c + '-' + CHAR(@x + ASCII('a'))

Why?


/*------------------------------------------------------------------*/
declare @x int;
declare @c char(100);

set @c = ''
set @x = -1;
while @x <25
begin
SET @x = @x + 1
set @c = CHAR(@x + ASCII('a')) + '-' + @c
end
print @c
/*------------------------------------------------------------------*/

When I use

set @c = CHAR(@x + ASCII('a')) + '-' + @c

it returns:

z-y-x-w-v-u-t-s-r-q-p-o-n-m-l-k-j-i-h-g-f-e-d-c-b-a-

But if I use

set @c = @c + '-' + CHAR(@x + ASCII('a'))

it returned nothing.

Q2)
Also, how can I get result like:

a-b-c-d-..........-z

Q3)
In addition, how can I get result like:

1a-2b-3c-.....-26z

Thank you in advance.

SQL Learner

SQL Learner wrote:
This is because you declared @c as CHAR(100). When you initialize it with '' it actually becomes 100 spaces as it is
CHAR data type. Then when you append to it goes beyond the 100 char limit and gets trimmed.

To solve it you can change to VARCHAR(100) or use RTRIM:

SET @c = RTRIM(@c) + '-' + CHAR(@x + ASCII('a'));


If you want to use a WHILE loop here it is:

DECLARE @x INT;
DECLARE @c VARCHAR(100);

SET @c = '';
SET @x = 96;

WHILE @x < 122
BEGIN
SET @x = @x + 1;
SET @c = @c + CASE WHEN @c = '' THEN '' ELSE '-' END + CHAR(@x);
END

PRINT @c;

You can do the same in set based query using table with numbers and FOR XML PATH in SQL Server 2005/2008:

SET @c = STUFF(
(SELECT '-' + CHAR(number)
FROM master..spt_values
WHERE type = 'P'
AND number BETWEEN 97 AND 122
ORDER BY number
FOR XML PATH('')), 1, 1, '');

PRINT @c;

Here are the two methods with WHILE and FOR XML PATH:

DECLARE @x INT;
DECLARE @c VARCHAR(100);

SET @c = '';
SET @x = 96;

WHILE @x < 122
BEGIN
SET @x = @x + 1;
SET @c = @c + CASE WHEN @c = '' THEN '' ELSE '-' END + CAST(@x - 96 AS VARCHAR(10)) + CHAR(@x);
END

PRINT @c;

SET @c = STUFF(
(SELECT '-' + CAST(number - 96 AS VARCHAR(10)) + CHAR(number)
FROM master..spt_values
WHERE type = 'P'
AND number BETWEEN 97 AND 122
ORDER BY number
FOR XML PATH('')), 1, 1, '');

PRINT @c;

--
Plamen Ratchev
http://www.SQLStudio.com

Plamen,

It is incredible that you can provide such accurate lengthy answer in
such short period (16 minutes). Thanks.

I may come back to you for FOR XML PATH, but for now. The first
method you provided for each of the questions is enough.

Thanks again!

SQL Learner