Problem with Case statement

not sure what's missing here but it's not liking my select with parameter check. The syntax is wrong here but not sure what.

CASE @.BKYChapter

WHEN 7 THEN

Insert into dbo.E_Final

(TransactionDate, TransactionTime, AccountNumber, TransactionCode, FieldCode, NewValue, InternalExternalFlag, RecovererCode, AS_400_UserID, ProductLoanTypeCode, NotUsed)

Select GetDate(), GetDate(), @.AcctNumber, 'MT', fc.R_FieldCode, 'BK07', 'I', ' ', ' ', ' ', ' ' FROM dbo.E_Field_FieldCode

INNER JOIN dbo.E_Field_FieldCode fc ON fc.R_FieldName = 'RecoveryCode' GROUP BY fc.R_FieldCode

Insert into dbo.E_Final

(TransactionDate, TransactionTime, AccountNumber, TransactionCode, FieldCode, NewValue, InternalExternalFlag, RecovererCode, AS_400_UserID, ProductLoanTypeCode, NotUsed)

Select GetDate(), GetDate(), @.AcctNumber, 'MT', fc.R_FieldCode, '992', 'I', ' ', ' ', ' ', ' ' FROM dbo.E_Field_FieldCode

INNER JOIN dbo.E_Field_FieldCode fc ON fc.R_FieldName = 'StatusCode' GROUP BY fc.R_FieldCode

WHEN 13 THEN

Insert into dbo.E_Final

(TransactionDate, TransactionTime, AccountNumber, TransactionCode, FieldCode, NewValue, InternalExternalFlag, RecovererCode, AS_400_UserID, ProductLoanTypeCode, NotUsed)

Select GetDate(), GetDate(), @.AcctNumber, 'MT', fc.R_FieldCode, 'BK13', 'I', ' ', ' ', ' ', ' ' FROM dbo.E_Field_FieldCode

INNER JOIN dbo.E_Field_FieldCode fc ON fc.R_FieldName = 'RecoveryCode' GROUP BY fc.R_FieldCode

Insert into dbo.E_Final

(TransactionDate, TransactionTime, AccountNumber, TransactionCode, FieldCode, NewValue, InternalExternalFlag, RecovererCode, AS_400_UserID, ProductLoanTypeCode, NotUsed)

Select GetDate(), GetDate(), @.AcctNumber, 'MT', fc.R_FieldCode, '993', 'I', ' ', ' ', ' ', ' ' FROM dbo.E_Field_FieldCode

INNER JOIN dbo.E_Field_FieldCode fc ON fc.R_FieldName = 'StatusCode' GROUP BY fc.R_FieldCode

[

ELSE SET @.Error = 'Chapter not valid'

]

END

CASE is not a control of flow statement. It is an expression. You have to use if...else to perform control of flow.|||thanks much!

Problem with case statement

With the syntax below, why is field1a not "A" if field1 does not
contain "_"

SELECT Field1a = CASE WHEN (field1 LIKE '%_%') THEN (charindex('_',
field1)) ELSE 'A' END, field1
FROM [Table1]<chudson007@.hotmail.com> wrote in message
news:1125938525.701908.180610@.f14g2000cwb.googlegr oups.com...
> With the syntax below, why is field1a not "A" if field1 does not
> contain "_"
>
> SELECT Field1a = CASE WHEN (field1 LIKE '%_%') THEN (charindex('_',
> field1)) ELSE 'A' END, field1
> FROM [Table1]

See LIKE and "Pattern Matching in Search Conditions" in Books Online - an
underscore is a wildcard for any single character, so you need to escape it
for a literal match:

LIKE '%[_]%'

Another issue is that CASE is an expression, so it can only return a single
data type - as you've written it, it could return either an int or a char,
so you would get a data type conversion error. See "Result Types" under CASE
in Books Online - you'll need to decide on a single return type, or perhaps
CAST the integer to a character type:

SELECT
Field1a = CASE
WHEN (field1 LIKE '%[_]%') THEN cast((charindex('_',field1)) as char(2))
ELSE 'A' END,
field1
FROM [Table1]

Simon|||cheers simon|||(chudson007@.hotmail.com) writes:
> With the syntax below, why is field1a not "A" if field1 does not
> contain "_"
>
> SELECT Field1a = CASE WHEN (field1 LIKE '%_%') THEN (charindex('_',
> field1)) ELSE 'A' END, field1
> FROM [Table1]

In SQL _ is a wildcard for exactly one occurrance of one characater. Thus
%_% matches anything but the empty string. Change to %[_]% to get what you
want.

...by the way, CASE is an expression, not a statement, in SQL.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

Problem With Case Function

Hi! Guys
i have problem with order by clause.
i have table some T1 with two columns say Itemname and Status
and data like this
ItemName Status
T1 G
T2 D
T3 US
T4 S
T5 G
T6 NULL
T7 NULL
Now i want to show with status like
G
D
S
US
SELECT
CASE Status
WHEN 'G' THEN 0
WHEN 'NULL' THEN 0
WHEN 'D' THEN 1
WHEN 'S' THEN 2
WHEN 'US' THEN 3 END AS STATUSORDER
FROM T1
But it is not ececuting properly.it is not exact answer what i want.
plz , give solutionHi, shiva
You can use one of these queries:
SELECT
CASE
WHEN Status='G' THEN 0
WHEN Status IS NULL THEN 0
WHEN Status='D' THEN 1
WHEN Status='S' THEN 2
WHEN Status='US' THEN 3
END AS STATUSORDER
FROM T1
or:
SELECT
CASE Status
WHEN 'D' THEN 1
WHEN 'S' THEN 2
WHEN 'US' THEN 3
ELSE 0
END AS STATUSORDER
FROM T1
Razvan|||What does it mean "not properly" ?
CREATE TABLE #Test
(
col VARCHAR(2) NULL
)
INSERT INTO #Test VALUES ('G')
INSERT INTO #Test VALUES ('D')
INSERT INTO #Test VALUES ('US')
INSERT INTO #Test VALUES ('S')
INSERT INTO #Test VALUES ('G')
INSERT INTO #Test VALUES (null)
INSERT INTO #Test VALUES (null)
SELECT col FROM #TesT WHERE col IS NOT NULL
GROUP BY col
ORDER BY
CASE col
WHEN 'G' THEN 0
WHEN 'D' THEN 1
WHEN 'S' THEN 2
WHEN 'US' THEN 3 END
"shiva" <bany.shanker@.gmail.com> wrote in message
news:1133867516.797293.72880@.f14g2000cwb.googlegroups.com...
> Hi! Guys
>
> i have problem with order by clause.
>
> i have table some T1 with two columns say Itemname and Status
>
> and data like this
>
> ItemName Status
> T1 G
> T2 D
> T3 US
> T4 S
> T5 G
> T6 NULL
> T7 NULL
> Now i want to show with status like
> G
> D
> S
> US
> SELECT
> CASE Status
> WHEN 'G' THEN 0
> WHEN 'NULL' THEN 0
> WHEN 'D' THEN 1
> WHEN 'S' THEN 2
> WHEN 'US' THEN 3 END AS STATUSORDER
> FROM T1
> But it is not ececuting properly.it is not exact answer what i want.
> plz , give solution
>|||thanks Razvan,
it's working fine.
thanks a lot.

Problem with CASE Expression

Hi, all here,

I have a problem with CASE expression in my SQL staments.

the problem is:

when I tried to just partly update the column a , I used the CASE expression : set a=case when b=null then 'null' end

the result was strange: then all the values for column a turned to null.

so what is the problem tho?

Thanks a lot in advance for any guidance.

Is it something like this you're trying to do?

create table #x ( a int null, b int null )
go
insert #x
select 1, 1 union all
select 2, null union all
select 3, 1 union all
select 4, null
go
select * from #x
go

a b
-- --
1 1
2 NULL
3 1
4 NULL

(4 row(s) affected)

update #x
set a = case when b is null then null else a end
go

select * from #x
go

a b
-- --
1 1
NULL NULL
3 1
NULL NULL

(4 row(s) affected)

drop table #x
go

/Kenneth

|||set a=case when b IS null then 'null' end|||The issue is with your comparison of the column value against NULL using equality operator. By default, <any non null value> <> NULL unless you set ANSI_NULLS to off and this affects few operations in the server. You can check the Books Online for more details. The recommended syntax is to use the IS NULL or IS NOT NULL clauses for checking NULL values.

Problem with case expression

In the Portal1 case expression in the script at the bottom I would like
to replace where the result 1 is returned, with the substring function
returned as Portal

{SUBSTRING(Field1, CHARINDEX('tonep', Field1) + 4, (CHARINDEX('.txt',
Field1) - 8) - (CHARINDEX('tonep', Field1) + 4))}

However, I am experiencing errors. I think it is because The substring
function will not return a number as the case expression expects so I
must incorporate cast or convert, but do not know how. Can you help?

SELECT portal1 = CASE WHEN len(Field1) > 5 THEN 1 ELSE '' END,
SUBSTRING(Field1, CHARINDEX('tonep', Field1) + 4, (CHARINDEX('.txt',
Field1) - 8)
- (CHARINDEX('tonep', Field1) + 4)) AS portal,
Table.*
FROM TableOn 30 Sep 2005 02:02:28 -0700, chudson007@.hotmail.com wrote:

>In the Portal1 case expression in the script at the bottom I would like
>to replace where the result 1 is returned, with the substring function
>returned as Portal
>{SUBSTRING(Field1, CHARINDEX('tonep', Field1) + 4, (CHARINDEX('.txt',
>Field1) - 8) - (CHARINDEX('tonep', Field1) + 4))}
>However, I am experiencing errors. I think it is because The substring
>function will not return a number as the case expression expects so I
>must incorporate cast or convert, but do not know how. Can you help?
(snip)

Hi chudson007,

From your description, I don't understand what you're trying to achieve.
Maybe you could illustrate this some more by providing

- The table structure, posted as CREATE TABLE statements (irrelevant
colunms may be omitted, but please include all constraints)
- Some illustrative rows of sample data, posted as INSERT statements
- The expected output or results

See www.aspfaq.com/5006 for more details and some hints on how to
assemble this info.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi

As Hugo has requested DDL and sample data is really the only way to know
exactly what is happening.

But, when Field1 is less than 8 characters or does not contain ".txt" or
"tonep" you may get problems.

John

<chudson007@.hotmail.com> wrote in message
news:1128070948.064118.208110@.z14g2000cwz.googlegr oups.com...
> In the Portal1 case expression in the script at the bottom I would like
> to replace where the result 1 is returned, with the substring function
> returned as Portal
> {SUBSTRING(Field1, CHARINDEX('tonep', Field1) + 4, (CHARINDEX('.txt',
> Field1) - 8) - (CHARINDEX('tonep', Field1) + 4))}
> However, I am experiencing errors. I think it is because The substring
> function will not return a number as the case expression expects so I
> must incorporate cast or convert, but do not know how. Can you help?
>
>
> SELECT portal1 = CASE WHEN len(Field1) > 5 THEN 1 ELSE '' END,
> SUBSTRING(Field1, CHARINDEX('tonep', Field1) + 4, (CHARINDEX('.txt',
> Field1) - 8)
> - (CHARINDEX('tonep', Field1) + 4)) AS portal,
> Table.*
> FROM Table

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

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

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