Create a sql loop using alphabet characters

I have a basic while loop but I need to be able to increment the counter from one alpha character to the next:

declare @.counternvarchar(10)

set @.counter='A'

while @.counter<'Z'

begin

print'the counter is '+ @.counter

set @.counter= @.counter+ @.counter

end

In this example I would need to see the following output:

the counter is A
the counter is B
the counter is C
the counter is D
....

Is this possible in SQL?

Yes, of course, just like you would in most other languages. Either use an integer that represents the ascii value of the letter, increment by one in the loop, and when you need to reference it, use CHAR(@.counter)...

SET @.counter=Ascii('A')

WHILE @.counter<=Ascii('Z')

BEGIN

PRINT CHAR(@.counter)

SET @.counter=@.counter+1

END

Or use an integer, that is the index into a string that contains all the letters you want...

SET @.Letters='ABCDEFGHIJKLMNOPQRSTUVWXYZ'

SET @.counter=0

WHILE @.counter<length(@.Letters)

BEGIN

PRINT SUBSTRING(@.Letters,@.counter,1)

SET @.counter=@.counter+1

END

Or... use what you had and use the @.counter to store the character you want, and use the following to increment it:

SET @.counter=CHAR(ASCII(@.counter)+1)

|||

Thanks!

CR Ver 10 String to Memo Field

How can I convert a String Field to a Memo Field so I can display more then 255 characters on my report? I've tried the "Can Grow" option and it's not working.......After setting the can grow option, did you increase the field height?

CR + LF In Column Data

I have CR+LF characters in my data and I would like to replace them with nothing. I am trying to us a derived column transformation to get rid of the character but I am getting errors. Here is what I have tried so far:

REPLACE( [Column0],VBCrLf , "")

REPLACE( [Column0],chr(13)+chr(10) , "")

REPLACE( [Column0],char(13)+char(10) , "")

Anyone have any ideas?

Thanks!!!

I'm guessing that

REPLACE( [Column0],\r+\n , "")

will do the job.

Check out the escape characters listed here: http://msdn2.microsoft.com/en-us/library/ms141001.aspx

-Jamie

|||

Thanks for the quick response but that did not work either.

|||

Why not? It really helps to provide error messages in this situation.

-Jamie

|||

Sorry about that. Here is the error message(s).

TITLE: Microsoft Visual Studio

Error at Data Flow Task [Derived Column [711]]: Attempt to parse the expression "REPLACE( [Column0],\r+\n , "")" failed. The token "\" at line number "1", character number "21" was not recognized. The expression cannot be parsed because it contains invalid elements at the location specified.

Error at Data Flow Task [Derived Column [711]]: Cannot parse the expression "REPLACE( [Column0],\r+\n , "")". The expression was not valid, or there is an out-of-memory error.

Error at Data Flow Task [Derived Column [711]]: The expression "REPLACE( [Column0],\r+\n , "")" on "input column "Column0" (724)" is not valid.

Error at Data Flow Task [Derived Column [711]]: Failed to set property "Expression" on "input column "Column0" (724)".

ADDITIONAL INFORMATION:

Exception from HRESULT: 0xC0204006 (Microsoft.SqlServer.DTSPipelineWrap)

Do I need to enclose the \r+\n with quotes?

Thanks!

|||

DOH! Yes, you need to include quotes. Like this:

REPLACE( [Column0],"\r\n" , "")

Sorry, my mistake.

-Jamie

|||

That worked like a champ!

Thanks a lot for your help Jamie!!!!

Counting all the characters in a column

I'm a SQL newbie...'nuf said.

How do I use a SELECT statement to count all the characters in a given column? I would like to calcuate the total number of characters, the average length, etc.

I can use the LEN function to get a single row like this:

SELECTLEN(NAME1)

FROM INDEXINFO

WHERE NDX = 101

I played around with the COUNT function but that seems to be for counting rows. In VB I would use a FOR EACH clause and keep a running total for the result of the LEN function. Is this a problem better handled using a T-SQL script or can it be done with SELECT statement?

Thanks!

DeBug

Umm...I changed my key words in my Google search and figured out the following:

SELECTSUM(LEN(NAME1))FROM INDEXINFO

WHERE NDX < 100

Ok, this got me the total and I can of course change the WHERE clause but can I also get the AVE at the same time?

|||SELECT SUM(...), AVG(...) FROM INDEXINFO
WHERE NDX < 100|||

There are problems with using LEN(). See this thread for a recent discussion.

You could do the following:

Code Snippet

SELECT
ColumnLength = max( datalength( Name1 )),
ColumnAvg = avg( datalength( Name1 ))
FROM IndexInfo
WHERE NDX = 101

|||Thank you for your reply!|||

Hi Arnie,

You have to take in mind if the column is a unicode one, because for those, every character is 2 bytes.

AMB

|||

Arnie,

Thank you and Phil for helping me with this. Here is the final version:

SELECT

Name1 =sum(datalength( Name1 )),

Name2 =sum(datalength( name2 )),

Name3 =sum(datalength( name3 )),

Name4 =sum(datalength( name4 )),

Date =sum(datalength( date ))

FROM IndexInfo

SELECT

Name1 =avg(datalength( Name1 )),

Name2 =avg(datalength( name2 )),

Name3 =avg(datalength( name3 )),

Name4 =avg(datalength( name4 )),

Date =avg(datalength( date ))

FROM IndexInfo

btw, how do you do that code snippet?|||

I must have trailing spaces somewhere....I used both the len and datalength for the results to text:

Name1 Name2 Name3 Name4 Date

-- -- -- -- --

644653 1047334 59933 69820 888709

(1 row(s) affected)

Name1 Name2 Name3 Name4 Date

-- -- -- -- --

644597 1047332 59933 69820 888709

|||Look for the rows where the LEN() and DATALENGTH() don't match.|||

Great Reminder Alejandro,

DATALENGTH() reports the number of bytes used to store the field value, so when using DATALENGTH() with unicode (nchar(), nvarchar()) datatypes, it is necessary to divide by two if you are handling single byte characters.

|||

I wrapped the blanks in a delimiter to make it show a little better. I now need to review the data entry code to see why it let someone key all those blank spaces

Thanks again to all that helped!

DeBug

SELECT NDX, MY_BLANKS = ('-->' + NAME1 + '<--') FROM INDEXINFO

WHERE DATALENGTH(NAME1) > LEN(NAME1);

NDX MY_BLANKS
-- --
263 --> <--
427 --> <--

(2 row(s) affected)