Converting Text to Proper Text in SQL

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??(m.ramana@.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, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||
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(@.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|||Great, I was about to create one, appreciate your time.

converting mysql 2 sql 2000

how do i go about converting the Mysql Script below to Sql Server Script ??

CREATE TABLE cat (
id int NOT NULL auto_increment,
name char(20) NOT NULL default '',
PRIMARY KEY (id)
);

INSERT INTO cat VALUES (1,'Not Categorised');

ALTER TABLE cat AUTO_INCREMENT = 5;

i want a script that:
creates the table
inserts a row of data and then
sets the increment field to start at 5

i tried using identity(1,1) and
SET IDENTITY_INSERT cat ON;
INSERT INTO cat VALUES (1,'Not Categorised');

but when i enter the data in i get the error below

An explicit value for the identity column in table 'cat' can only be specified when a column list is used and IDENTITY_INSERT is ON.

please can anyone help or advice

thanksOriginally posted by m.inckle
how do i go about converting the Mysql Script below to Sql Server Script ??

CREATE TABLE cat (
id int NOT NULL auto_increment,
name char(20) NOT NULL default '',
PRIMARY KEY (id)
);

INSERT INTO cat VALUES (1,'Not Categorised');

ALTER TABLE cat AUTO_INCREMENT = 5;

i want a script that:
creates the table
inserts a row of data and then
sets the increment field to start at 5

i tried using identity(1,1) and
SET IDENTITY_INSERT cat ON;
INSERT INTO cat VALUES (1,'Not Categorised');

but when i enter the data in i get the error below

An explicit value for the identity column in table 'cat' can only be specified when a column list is used and IDENTITY_INSERT is ON.

please can anyone help or advice

thanks
create table as:
CREATE TABLE cat (
id int NOT NULL ,
name char(20) NOT NULL default '',
PRIMARY KEY (id)
);
then insert the record:
INSERT INTO cat VALUES (1,'Not Categorised');

then go to the enterprise manager->design table and set the identity property of the table to yes with value 5.|||Originally posted by harshal_in
create table as:
CREATE TABLE cat (
id int NOT NULL ,
name char(20) NOT NULL default '',
PRIMARY KEY (id)
);
then insert the record:
INSERT INTO cat VALUES (1,'Not Categorised');

then go to the enterprise manager->design table and set the identity property of the table to yes with value 5.

is there any way to do this in a script ???|||Originally posted by harshal_in
create table as:
CREATE TABLE cat (
id int NOT NULL ,
name char(20) NOT NULL default '',
PRIMARY KEY (id)
);
then insert the record:
INSERT INTO cat VALUES (1,'Not Categorised');

then go to the enterprise manager->design table and set the identity property of the table to yes with value 5.

is there a way of altering an identity column in a table like below

ALTER TABLE cat ALTER COLUMN id INT NOT NULL identity(5,1);

i always get
Incorrect syntax near the keyword 'identity'.|||Originally posted by m.inckle
is there a way of altering an identity column in a table like below

ALTER TABLE cat ALTER COLUMN id INT NOT NULL identity(5,1);

i always get
Incorrect syntax near the keyword 'identity'.
no. as far as my knowledge goes you can't add identity to an existing column thru alter table, u have to add a new column for it.
so the easiest way is to use enterprise manager.