Proper Syntax of CASE() ion a cell in RS?

Hello All;
I was wondering if there is a way to make decisions on a particular cell in a
report (Tabular of Matrix) where the "CASE" effect can be achieved. I use IIF
(SOMEHTING >0,"RED","Green") all over, but I wonder if the decision making
can be expanded on Cell's content so as to get the Effect of CASE ot IF
ElseIF Else as follows:
If (SOMTHING) Then (DoSomething)
(ElseIF DOSomethingElse1)
(ElseIF DoSomethingElse2)
Else
(DoOtherwise)
Thank you.
--
Message posted via http://www.sqlmonster.comThe best way is to write some code and call it.
In Report properties go to the code page and enter a VisualBasic .Net
function eg:
Function SetColour(ByVal N As Double) As String
Select N
Case 0
Return "White"
Case >0
Return "Blue"
Case Else
Return "Red"
End Select
End Function
then call the function in your expression to set the colour of a cell eg:
=Code.SetColour( Fields!CurrencyAmount.Value-Sum(Fields!AmountExpensed.Value))
"James Woo via SQLMonster.com" wrote:
> Hello All;
> I was wondering if there is a way to make decisions on a particular cell in a
> report (Tabular of Matrix) where the "CASE" effect can be achieved. I use IIF
> (SOMEHTING >0,"RED","Green") all over, but I wonder if the decision making
> can be expanded on Cell's content so as to get the Effect of CASE ot IF
> ElseIF Else as follows:
> If (SOMTHING) Then (DoSomething)
> (ElseIF DOSomethingElse1)
> (ElseIF DoSomethingElse2)
> Else
> (DoOtherwise)
> Thank you.
> --
> Message posted via http://www.sqlmonster.com
>|||Mary .. Thank you :), very nice code.
--
Message posted via http://www.sqlmonster.com|||Based on the information below I have been able to create code, however I
need to test for a value between 2 numbers..ex
Case < 366
Return "1 yr or less"
Case >0 and <731
Return "1-2 years"
I am running into an issue with line 3. Is it possible? Am I using the
wrong syntax? If this is not the best approach what would that be?
Thank you for any guidance.
"Mary Bray [SQL Server MVP]" wrote:
> The best way is to write some code and call it.
> In Report properties go to the code page and enter a VisualBasic .Net
> function eg:
> Function SetColour(ByVal N As Double) As String
> Select N
> Case 0
> Return "White"
> Case >0
> Return "Blue"
> Case Else
> Return "Red"
> End Select
> End Function
> then call the function in your expression to set the colour of a cell eg:
> =Code.SetColour( Fields!CurrencyAmount.Value-Sum(Fields!AmountExpensed.Value))
> "James Woo via SQLMonster.com" wrote:
> > Hello All;
> >
> > I was wondering if there is a way to make decisions on a particular cell in a
> > report (Tabular of Matrix) where the "CASE" effect can be achieved. I use IIF
> > (SOMEHTING >0,"RED","Green") all over, but I wonder if the decision making
> > can be expanded on Cell's content so as to get the Effect of CASE ot IF
> > ElseIF Else as follows:
> > If (SOMTHING) Then (DoSomething)
> > (ElseIF DOSomethingElse1)
> > (ElseIF DoSomethingElse2)
> > Else
> > (DoOtherwise)
> > Thank you.
> >
> > --
> > Message posted via http://www.sqlmonster.com
> >|||I was able to find some information, this may helps others...
Case < 366
Return "1 yr or less"
Case 0 To 731
Return "1-2 years"
"DigitalVixen" wrote:
> Based on the information below I have been able to create code, however I
> need to test for a value between 2 numbers..ex
> Case < 366
> Return "1 yr or less"
> Case >0 and <731
> Return "1-2 years"
> I am running into an issue with line 3. Is it possible? Am I using the
> wrong syntax? If this is not the best approach what would that be?
> Thank you for any guidance.
> "Mary Bray [SQL Server MVP]" wrote:
> > The best way is to write some code and call it.
> > In Report properties go to the code page and enter a VisualBasic .Net
> > function eg:
> > Function SetColour(ByVal N As Double) As String
> > Select N
> > Case 0
> > Return "White"
> > Case >0
> > Return "Blue"
> > Case Else
> > Return "Red"
> > End Select
> > End Function
> >
> > then call the function in your expression to set the colour of a cell eg:
> >
> > =Code.SetColour( Fields!CurrencyAmount.Value-Sum(Fields!AmountExpensed.Value))
> >
> > "James Woo via SQLMonster.com" wrote:
> >
> > > Hello All;
> > >
> > > I was wondering if there is a way to make decisions on a particular cell in a
> > > report (Tabular of Matrix) where the "CASE" effect can be achieved. I use IIF
> > > (SOMEHTING >0,"RED","Green") all over, but I wonder if the decision making
> > > can be expanded on Cell's content so as to get the Effect of CASE ot IF
> > > ElseIF Else as follows:
> > > If (SOMTHING) Then (DoSomething)
> > > (ElseIF DOSomethingElse1)
> > > (ElseIF DoSomethingElse2)
> > > Else
> > > (DoOtherwise)
> > > Thank you.
> > >
> > > --
> > > Message posted via http://www.sqlmonster.com
> > >

proper or title case function?

I know there are flaws with this type of function (lots of exceptions to
handle such as when "John Jones III" becomes "John Jones Iii" or "Detroit,
MI" becomes "Detroit, Mi") and some of you might yell at me for wanting to
do this type of formatting on the backend, but I was wondering if there is a
proper or title case type of function. Of course there's UPPER and LOWER but
I can't find a PROPER or TITLE. If you don't know what I mean, it's where
the first letter of every word is in upper case and the reset of the letters
are in lower case (MS Word calls it "Title Case"). Just wondering.
Thanks,
KeithKeith,
You're right this is probably better for the middle-tier or client however
see:
http://www.aspfaq.com/show.asp?id=2299
HTH
Jerry
"Keith G Hicks" <krh@.comcast.net> wrote in message
news:e7TnenN2FHA.1100@.TK2MSFTNGP15.phx.gbl...
>I know there are flaws with this type of function (lots of exceptions to
> handle such as when "John Jones III" becomes "John Jones Iii" or "Detroit,
> MI" becomes "Detroit, Mi") and some of you might yell at me for wanting to
> do this type of formatting on the backend, but I was wondering if there is
> a
> proper or title case type of function. Of course there's UPPER and LOWER
> but
> I can't find a PROPER or TITLE. If you don't know what I mean, it's where
> the first letter of every word is in upper case and the reset of the
> letters
> are in lower case (MS Word calls it "Title Case"). Just wondering.
> Thanks,
> Keith
>

Proper Case Syntax on Name Column

Hello,
I have a Column called Name varchar(50). Data was imported from an excel
spreadsheet in all caps. How can proper case all fields in this column that
is in CAPS?The LOWER function (see BOL) can be used in an UPDATE statement to change
the contents of this field to lower case.
HTH
J
"Lontae Jones" <LontaeJones@.discussions.microsoft.com> wrote in message
news:36B0E293-69CC-4378-95CC-1557753BA424@.microsoft.com...
> Hello,
> I have a Column called Name varchar(50). Data was imported from an excel
> spreadsheet in all caps. How can proper case all fields in this column
> that
> is in CAPS?|||If you mean literally "Proper" case (betty sue changed to Betty Sue), you ha
ve
to roll your own using the Upper and Lower functions. Here's one I dug with
a
quick Google search:
[url]http://www.novicksoftware.com/UDFofW/Vol1/T-SQL-UDF-Volume-1-Number-47-proper-c
ase.htm[/url]
Thomas
"Lontae Jones" <LontaeJones@.discussions.microsoft.com> wrote in message
news:36B0E293-69CC-4378-95CC-1557753BA424@.microsoft.com...
> Hello,
> I have a Column called Name varchar(50). Data was imported from an excel
> spreadsheet in all caps. How can proper case all fields in this column th
at
> is in CAPS?|||And whatever solution you find, you have to realize that there will still be
errors.
My last name can be spelled D'anjou d'Anjou or D'Anjou.
"Thomas Coleman" <replyingroup@.anywhere.com> wrote in message
news:%23Yh2NxRbFHA.2996@.TK2MSFTNGP10.phx.gbl...
> If you mean literally "Proper" case (betty sue changed to Betty Sue), you
> have to roll your own using the Upper and Lower functions. Here's one I
> dug with a quick Google search:
> [url]http://www.novicksoftware.com/UDFofW/Vol1/T-SQL-UDF-Volume-1-Number-47-proper
-case.htm[/url]
>
> Thomas
>
>
> "Lontae Jones" <LontaeJones@.discussions.microsoft.com> wrote in message
> news:36B0E293-69CC-4378-95CC-1557753BA424@.microsoft.com...
>

Proper case of a field

Is there a way which helps to standerdize the case of a field e.g. I have a
field called location name which is sometimes all in caps or in lower cases,
I need to format it to be like CAMBRDIGE becomes Cambridge and etc
ThanksI'd guess you want to create a user defined function that would do
this.
Pseudo-code...
create function dbo.udfProperCase( @.value varchar(100) )
returns varchar(100)
begin
declare @.i int
declare @.valueLen int
declare @.properCaseValue varchar(100)
set @.valueLen = len(@.value)
set @.i = 0
set @.properCaseValue = ''
while @.i < @.valueLen
begin
-- test for beginning of a word, make it uppercase, etc...
set @.properCaseValue = @.properCaseValue + whatever
set @.i = @.i + 1
end
return @.properCaseValue
end
You could then use it as such:
insert into MyTable( col1, col2 ) values( 'abc', dbo.udfProperCase(
'cambridge' ) )|||Thanks man! Looks . One more question. Where should i bring in the field
from the database, i mean
SET @.valueLen = len(@.value) '
OR
SET @.properCaseValue = @.properCaseValue + whatever '
How will this set my fields with proper cases like
dbo.udfProperCase('cambridge' )
Will this become Cambrige?
"bd" wrote:

> I'd guess you want to create a user defined function that would do
> this.
> Pseudo-code...
> create function dbo.udfProperCase( @.value varchar(100) )
> returns varchar(100)
> begin
> declare @.i int
> declare @.valueLen int
> declare @.properCaseValue varchar(100)
> set @.valueLen = len(@.value)
> set @.i = 0
> set @.properCaseValue = ''
> while @.i < @.valueLen
> begin
> -- test for beginning of a word, make it uppercase, etc...
> set @.properCaseValue = @.properCaseValue + whatever
> set @.i = @.i + 1
> end
> return @.properCaseValue
> end
>
> You could then use it as such:
> insert into MyTable( col1, col2 ) values( 'abc', dbo.udfProperCase(
> 'cambridge' ) )
>|||The body of the function wouldn't use the field from the database. You
could do the following:
insert into MyTable( col1, col2 )
select col1, dbo.udfProperCase( mycol ) from AnotherTable
In this example, the value of the field 'mycol' is passed into your
function. The function manipulates the value (in this case, makes it a
"proper case") and returns it.
Or you could do this to test your function:
print dbo.udfProperCase( 'this is my test' )
And it would use the literal string value.
Of course, the function I wrote is just pseudo-code. You'll have to
code the particulars of it yourself because, unfortunately, I don't
have a function like that in my toolbox to copy and paste for you.
I hope this helps.
Bryce|||It is certainly. One more thing, should I write upper and lower case
functions on the variable @.value '
"bd" wrote:

> The body of the function wouldn't use the field from the database. You
> could do the following:
> insert into MyTable( col1, col2 )
> select col1, dbo.udfProperCase( mycol ) from AnotherTable
> In this example, the value of the field 'mycol' is passed into your
> function. The function manipulates the value (in this case, makes it a
> "proper case") and returns it.
> Or you could do this to test your function:
> print dbo.udfProperCase( 'this is my test' )
> And it would use the literal string value.
> Of course, the function I wrote is just pseudo-code. You'll have to
> code the particulars of it yourself because, unfortunately, I don't
> have a function like that in my toolbox to copy and paste for you.
> I hope this helps.
> Bryce
>|||There is no need to use loops, proprietary or procedural code. SQL is
a declarative language, so use the functions instead. This usually
runs faster and it is portable.
CREATE FUNCTION TitleCase (@.input_title VARCHAR(100))
RETURNS VARCHAR(100)
RETURN
REPLACE (' a', ' A',
REPLACE (' b', ' B',
REPLACE ...
REPLACE(' z', ' Z',
(UPPER (SUBSTRING (@.input_title, 1, 1) + LOWER (SUBSTRING
(@.input_title, 2))
. ),
),
);
The nested REPLACE() functions capitalize the first letters of each
word. The intermost substring concatenations capitalize the first word.
I would also remove extra spaces in the same routine.|||Thanks buddy, looks great but one question..Could you explain a little how I
can use your replace in my version?
Do i have to declare some variables to use here?
Thanks
"--CELKO--" wrote:

> There is no need to use loops, proprietary or procedural code. SQL is
> a declarative language, so use the functions instead. This usually
> runs faster and it is portable.
> CREATE FUNCTION TitleCase (@.input_title VARCHAR(100))
> RETURNS VARCHAR(100)
> RETURN
> REPLACE (' a', ' A',
> REPLACE (' b', ' B',
> REPLACE ...
> REPLACE(' z', ' Z',
> (UPPER (SUBSTRING (@.input_title, 1, 1) + LOWER (SUBSTRING
> (@.input_title, 2))
> .. ),
> ),
> );
> The nested REPLACE() functions capitalize the first letters of each
> word. The intermost substring concatenations capitalize the first word.
> I would also remove extra spaces in the same routine.
>|||Put it in a UPDATE statement SET clause to clena up the data first.
then add a CHECK() constraint like this:
CHECK (<horrible expression> = my_column) so this will not happen
again.sql

proper case for name in sql server

hi,
a field with all capital letters for last name and frist name -- DAVID
JONES
I need a funcation taht I could retrive it from sql query analyzer
select propercase(field1) from names. it returns "David Jones"
THnaksHi
A google search would turn up many hits such as
http://vyaskn.tripod.com/code/propercase.txt but it doesn't cater for all
names e.g MacNeil, McNeil, O'Neill etc..
John
"mecn" wrote:
> hi,
> a field with all capital letters for last name and frist name -- DAVID
> JONES
> I need a funcation taht I could retrive it from sql query analyzer
> select propercase(field1) from names. it returns "David Jones"
> THnaks
>
>

Proper Case

Is there any way to convert to Proper Case in SQL Server? for lower case we
have LOWER and for upper case we have UPPER like that is there any thing for
proper case? if not how can we do this?You could always use a combination of the substr function. I think that
is the name in Oracle.
HTH|||xsluser wrote:
> Is there any way to convert to Proper Case in SQL Server? for lower
> case we have LOWER and for upper case we have UPPER like that is
> there any thing for proper case? if not how can we do this?
Best to do this on the client. But if you have to, you could write a
function to parse the string and turn it into proper case. Most dev tools
have a built-in PROPER function and this would be my recommendation of where
this type of formatting should be done. Or even better would be to get the
users to enter the information in the proper case to begin with.
David Gugick
Quest Software