m.******@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(@pati ndx1, @my_str) + PatIndex(@patin dx2, @my_str) +
PatIndex(@patin dx3, @my_str) + PatIndex(@patin dx4, @my_str) = 0)
Begin
Select
@spc = Len(@my_str)
End
Else
Begin
Select @spc = PatIndex(@patin dx1, @my_str)
If @spc = 0 Or (PatIndex(@pati ndx2, @my_str) < @spc
And PatIndex(@patin dx2, @my_str) > 0)
Select @spc = PatIndex(@patin dx2, @my_str)
If @spc = 0 Or (PatIndex(@pati ndx3, @my_str) < @spc
And PatIndex(@patin dx3, @my_str) > 0)
Select @spc = PatIndex(@patin dx3, @my_str)
If @spc = 0 Or (PatIndex(@pati ndx4, @my_str) < @spc
And PatIndex(@patin dx4, @my_str) > 0)
Select @spc = PatIndex(@patin dx4, @my_str)
End
Select
@word_str = Left(@my_str, @spc)
Select
@this_str = @this_str + Upper(Left(@wor d_str,1)) +
Lower(SubString (@word_str,2,@s pc))
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 
