Hey all,

I'm trying to use the INITCAP function to make the first letter of an
address appear in capital and the rest in lower case, but it seems to
appear all in Caps.

Can anyone please help. I've attached a snipplet of the code.

TIA

Dhwiren


DECLARE @counter smallint
DECLARE @counterLimit smallint
DECLARE @RandomNumber float
DECLARE @RandomNumberInt tinyint
DECLARE @CurrentCharacter varchar(1)
DECLARE @ValidCharacters varchar(255)
DECLARE @ValidCharactersLength int
DECLARE @DEBUG int

SET @ValidCharacters = 'aaabcdeeefghiiijklmnooopqrstuuuvwxyz'
SET @ValidCharactersLength = len(@ValidCharacters)
SET @CurrentCharacter = ''
SET @RandomNumber = 0
SET @RandomNumberInt = 0
SET @DEBUG=1


-- @AddressLine1
SET @counter=0
SET @counterLimit = len(@AddressLine1)
IF @counterLimit>0
BEGIN
SET @AddressLine1=''
WHILE @counter < @counterLimit
BEGIN
-- create a random sting
SET @RandomNumber = Rand()
SET @RandomNumberInt = Convert(tinyint,
((@ValidCharactersLength - 1) * @RandomNumber + 1))
SELECT @CurrentCharacter = SUBSTRING(@ValidCharacters,
@RandomNumberInt, 1)
IF @counter = 1 SET @CurrentCharacter =(@CurrentCharacter)
SET @counter = @counter + 1
SET @AddressLine1 = @AddressLine1 + INITCAP(@CurrentCharacter)
END
IF @DEBUG=1 PRINT @AddressLine1
END

i havent ran the code, but your logic is slightly flawed. change the end part
to something like this. You can also use UPPER in place of INITCAP in your
code because you are pulling 1 character at a time from SUBSTRING.


IF @counter = 1 SET @CurrentCharacter = UPPER(@CurrentCharacter)
SET @counter = @counter + 1
SET @AddressLine1 = @AddressLine1 + @CurrentCharacter

--
Andy Price,
Sr. Database Administrator,
MCDBA 2003


"Dwizz" wrote:

Thank you for the quick reply Andy,
I am pulling 1 character out at a time but in the database they are all
uppercase,
which is why I was wondering using INITCAP

Dhwiren

Andy Price wrote:

I see, I didnt realize they were all in uppercase. You could change it to use
that, or you can just change your code to this (ie, added 1 line of code
prior to the block of code in the last post). INITCAP doesnt exist in SQL
Server. I am assuming you are an ORACLE or MySQL guy also?


set @CurrentCharacter =
LOWER(@CurrentCharacter)
IF @counter = 1 SET @CurrentCharacter = UPPER(@CurrentCharacter)
SET @counter = @counter + 1
SET @AddressLine1 = @AddressLine1 + @CurrentCharacter

--
Andy Price,
Sr. Database Administrator,
MCDBA 2003


"Dwizz" wrote:

Thanks Andy,
We got it to work, the extra line did it, I am indeed an Oracle man
Thanks once again Andy

Dhwiren

Ahhh.. Andy, I just noticed that its the second character that becomes
Capitalised and not the first, but as a bonus all the other characters
are in lower case. So how would I make the first character Capitalised
and not the second character capitalised