Converting Text to Proper Text in SQL

[m.ramana]

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??

[Erland Sommarskog]

(m.******@gmail.com) writes:

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'
I though "Cat in the Hat" was the proper title text in English,
and "Cat In The Hat" is what you get when you use a computer?

SQL Server is not strong on text maninpulation. You would have to loop
over the string, either one by one, or possibly piece by piece with
charindex(). There is no built-in for this.
Curious about few things, Does sql have Instr OR Split(like VB)
functionality

I would encourage you to look at Functions->String Functions in the
T-SQL References in Books Online. There you can learn about all functions
to manipulate strings in T-SQL.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[dbmonitor]

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(@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(@word_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

[m.ramana]

Great, I was about to create one, appreciate your time.