Prev: Who am I just login server
Next: SQL 2008 and Window 7
From: SQL Learner on 8 Feb 2010 21:52
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

From: Plamen Ratchev on 8 Feb 2010 22:08
SQL Learner wrote:
> 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.

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'));


>
> Q2)
> Also, how can I get result like:
>
> a-b-c-d-..........-z
>

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;

> Q3)
> In addition, how can I get result like:
>
> 1a-2b-3c-.....-26z
>

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
From: SQL Learner on 8 Feb 2010 22:31
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

 | 
Pages: 1
Prev: Who am I just login server
Next: SQL 2008 and Window 7