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 fieldfrom 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
ndinakar wrote: