Hi,
I integrated a third-party sw that is apparently used in hundreds of sites into my site. That sw has all field names in (Access) db in capital letters and uses small letters in sql statements in the code and that creates a problem on my site. The third-party sw developers say that this is caused by a false setting on the Web Server so they suggest I change my hosting. I know that if the field names and the sql statements in the code are both capital letters or both small letters everything works.
Does anybody have any idea about how to solve this problem? Is it true that it is a hosting problem? And if so could anybody please tell me how to correct web server setting?
Thanks a lot in advance,
CananWhat errors are you getting? It doesn't sound like a web setting to me. It sounds like typical third-party passing the buck crap.
It sounds like your web hosting company has a case-sensitive version of SQL Server. Either that or Access is case sensitive (not even sure if you can do that)??? :)
I think it's safe to say if it works by correcting the code it's not your webhost though.|||Thanks for your quick response. We found out that after v2.5 of MDAC this problem occurs if the server setting is Turkish. So this was our problem. And Microsoft Support pages suggest we use db field names and the sql statement in the same case to solve this problem if the server has Turkish settings ! We changed the Turkish settings on the server back to English but no luck the problem persisted. So what we did we changed the field names to small letters ! Not a nice way of solving a problem but works out... :)
Thanks again,
Canan
Showing posts with label capital. Show all posts
Showing posts with label capital. Show all posts
Friday, March 30, 2012
Saturday, February 25, 2012
Problem Using INITCAP function
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
ENDi havent ran the code, but your logic is slightly flawed. change the end par
t
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:
> 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
>|||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:[vbcol=seagreen]
> i havent ran the code, but your logic is slightly flawed. change the end p
art
> 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:
>|||I see, I didnt realize they were all in uppercase. You could change it to us
e
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:
> 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:
>|||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
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
ENDi havent ran the code, but your logic is slightly flawed. change the end par
t
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:
> 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
>|||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:[vbcol=seagreen]
> i havent ran the code, but your logic is slightly flawed. change the end p
art
> 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:
>|||I see, I didnt realize they were all in uppercase. You could change it to us
e
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:
> 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:
>|||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
Problem Using INITCAP function
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:
> 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
>
|||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:[vbcol=seagreen]
> 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:
|||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:
> 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:
>
|||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
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:
> 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
>
|||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:[vbcol=seagreen]
> 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:
|||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:
> 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:
>
|||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
Problem Using INITCAP function
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
ENDi 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:
> 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
>|||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 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:
> > 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 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:
> 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 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:
> >
> > > 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
> > >
> > >
>|||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
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
ENDi 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:
> 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
>|||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 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:
> > 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 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:
> 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 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:
> >
> > > 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
> > >
> > >
>|||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
Subscribe to:
Posts (Atom)