| dbmonitor 2005-05-27, 7:23 am |
|
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
|