Re: Converting Text to Proper Text in SQL

m.ramana@gmail.com  wrote:
> Given a string it should convert it to a proper text.
> Example: if you passed a string 'Cat in the hat', I want 'Cat In The
> Hat'
>
> Curious about few things, Does sql have Instr OR Split(like VB)
> functionality
>
> Anybody can help??

CREATE   function TitleCase
(
@my_str as varchar(8000)
)
Returns varchar (8000)
AS
Begin
Declare @this_str as varchar(8000)
Declare @word_str as varchar(5000)
Declare @spc int
Declare @patindx1 as varchar(100)
Declare @patindx2 as varchar(100)
Declare @patindx3 as varchar(100)
Declare @patindx4 as varchar(100)
select @patindx1 = '%[ -"/().]%'
select @patindx2 = '%[''][^s]%'
select @patindx4 = '%[''][s][a-z]%'
select @patindx3 = '%[0123456789][^snrt][^tdh]%'
Select @this_str = ''

Select
@my_str =  LTrim(RTrim(@my_str)
)


While Len(@my_str) > 0
Begin
if  (PatIndex(@patindx1,
 @my_str) + PatIndex(@patindx2, @my_str) +
PatIndex(@patindx3, @my_str) + PatIndex(@patindx4, @my_str) = 0)
Begin
Select
@spc = Len(@my_str)
End
Else
Begin
Select @spc = PatIndex(@patindx1, @my_str)
If @spc = 0 Or  (PatIndex(@patindx2,
 @my_str) < @spc
And PatIndex(@patindx2, @my_str) > 0)
Select @spc = PatIndex(@patindx2, @my_str)
If @spc = 0 Or  (PatIndex(@patindx3,
 @my_str) < @spc
And PatIndex(@patindx3, @my_str) > 0)
Select @spc = PatIndex(@patindx3, @my_str)
If @spc = 0 Or  (PatIndex(@patindx4,
 @my_str) < @spc
And PatIndex(@patindx4, @my_str) > 0)
Select @spc = PatIndex(@patindx4, @my_str)

End

Select
@word_str = Left(@my_str, @spc)

Select
@this_str = @this_str +  Upper(Left(@word_str
,1)) +
 Lower(SubString(@wor
d_str,2,@spc))
select @my_str = Right(@my_str, (Len(@my_str)-(@spc)))
End
Return (@this_str)
End

--
David Rowland
NEW DBMonitor Out Now!  New Email Alert Feature!
http://dbmonitor.tripod.com