SQL Query Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

SQL Query Help

Author

Topic

Tracey
Starting Member

40 Posts

Posted - 2004-07-01 : 04:44:04

Hi Everyone
I want the first Character and all the first character after space in a string in Uppercase.

I have written this query which does the job:

declare @input varchar(20)
declare @position int
set @input = 'hello box cello dello'
set @position = 1
SET @input = REPLACE(@input,substring(@input,1,1),upper(substring(@input,1,1)))
WHILE @position <= DATALENGTH(@input)

Begin
If ASCII(SUBSTRING(@input, @position, 1)) = 32
SET @input = REPLACE(@input,substring(@input,@position+1,1),upper(substring(@input,@position+1,1)))
set @position = @position + 1
End
print @input

Is there any string function which does this job?
Any help much appreciated

Thanks
Tracey

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-07-01 : 04:56:40

none that i can think of...

Go with the flow & have fun! Else fight the flow :)

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2004-07-01 : 05:12:50

Ugly, but more efficient, it uses charindex to jump to next ' ', rather than go through each char.


declare @input varchar(20)
declare @position int
set @input = 'hello box cello dello'
set @position = 1
SET @input = REPLACE(@input,substring(@input,1,1),upper(substring(@input,1,1)))
WHILE @position < DATALENGTH(@input)
Begin
set @position = charindex(' ',@input,@position+1)
if @position = 0
	begin
	set @position=DATALENGTH(@input)
	goto done
	end
SET @input = REPLACE(@input,substring(@input,@position+1,1),upper(substring(@input,@position+1,1))) 
End
done:
print @input

ciao

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2004-07-01 : 05:14:31

I suppose you could remove the "set @position=DATALENGTH(@input)" from the position=0 check, and just "go to done"

CiaO

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

Tracey
Starting Member

40 Posts

Posted - 2004-07-01 : 20:59:26

Thanks Guys

Tracey
Starting Member

40 Posts

Posted - 2004-07-01 : 23:12:00

Well,
The code wont be helpful when a Starting character has more than one presence.

For ex: "eelle coc bob"
this will result in "EEllE CoC BoB" but i want it to be "Eelle Coc Bob"

any ideas?

Tracey

Tracey
Starting Member

40 Posts

Posted - 2004-07-02 : 02:38:14

Ok guys, i have finally came up with this. it works ok.
Buts its ugly.I want all your comments on this, it took nearly 2 hrs to come to final logic and code.
sorry i couldnt comment the codes.
If there is any better way, iam more happy to see it.

declare @input varchar(44) -- gets the string value
declare @input_in varchar(44) -- Stores output string value
declare @position tinyint
declare @spaceindex tinyint

set @input = 'ellee bobb cecco dodddd'
set @position = 1
set @spaceindex = 0

while @position < DATALENGTH(@input)

BEGIN

if @position = 1
Begin
SET @input_in = UPPER(substring(@input,@position,@position))
Set @spaceindex = Charindex(' ',@input,1)
set @input_in = @input_in + substring(@input,@position+1,@spaceindex-1)
SET @position = @spaceindex
END

IF @position = 0
GOTO done
ELSe

Begin
SET @input_in = @input_in + upper(substring(@input,@position+1,1))
SET @spaceindex = charindex(' ',@input,@spaceindex+1)

IF @spaceindex = 0
SET @input_in = @input_in+substring(@input,@position+2,(datalength(@input))-(@position+1))
ELSE
SET @input_in = @input_in + substring(@input,@position+2,((@spaceindex-1)-(@position+1)))+' '

END
SET @position = @spaceindex
END

DONE:
Print 'The Original String is: ' + @input
print 'The Output is:' + @input_in

Thanks
Tracey

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2004-07-02 : 03:31:24

try this one:


declare @input varchar(20)
declare @position int
set @input = 'eelle coc bob'
set @position = 1
SET @input = upper(substring(@input,1,1))+(substring(@input,2,len(@input)-1))
WHILE @position < DATALENGTH(@input)
Begin
set @position = charindex(' ',@input,@position+1)
if @position = 0
	goto done
SET @input = REPLACE(@input,substring(@input,@position,2),upper(substring(@input,@position,2))) 
End
done:
print @input

checked it with your case, and also double spaces etc.

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

Kristen
Test

22859 Posts

Posted - 2004-07-02 : 03:32:09

Could you use a "splitter" function to convert the string to a temporary table, splitting on spaces, then captialise the first character of all rows in that temporary table, then concatenate them, with a space between, to get the output string. (You might wind up with any multiple-spaces converted to single spaces, depends how the splitter handles that!)

I reckon that would be "set based" and thus should be more efficient, but only a test would say for sure.

(The fastest splitter that I know of needs a table of integers from 1 - 8,000 and splits using set based logic. The code is on SQLTeam)

Kristen

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-07-02 : 05:17:12

Wanderer's last solution will be more efficient, but here is what I came up with while playing with it.


DECLARE @sql NVARCHAR(4000)
DECLARE @input VARCHAR(20)
DECLARE @output VARCHAR(20)
SET @input = 'eelle coc bob'

SELECT @sql = 'SELECT @o = Coalesce (@o + '' '' + words,words) 
FROM (SELECT Upper(Left(Words,1)) + Substring(words,2,len(words) - 1) words 
FROM (Select Words = ''' + REPLACE(@input, ' ', ''' UNION ALL SELECT ''') + ''') x ) y'

EXEC sp_executesql @sql, N'@o varchar(20) output', @output OUTPUT

select @output
                     
-------------------- 
Eelle Coc Bob

Kristen
Test

22859 Posts

Posted - 2004-07-02 : 13:37:20

SQL needs a decent Regular Expression find&replace engine ...

Kristen

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-07-02 : 14:44:51

Here is a function for Proper Case

http://vyaskn.tripod.com/code/propercase.txt

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-07-02 : 14:51:57

Are you doing this to one record at a time?

Or are you planning on updating everything in a record set?

This may be of some help:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=36527

Corey

Pat Phelan
Posting Yak Master

187 Posts

Posted - 2004-07-02 : 16:36:32

Don't mind the size, this includes the test script too:

DROP FUNCTION dbo.fCapFirst
GO

CREATE FUNCTION dbo.fCapFirst(@pcSource NVARCHAR(4000)) RETURNS NVARCHAR(4000)
AS BEGIN

DECLARE @i		INT
,  @cWork		NVARCHAR(4000)

SET @cWork = Reverse(Upper(Left(@pcSource, 1)) + Lower(SubString(@pcSource, 2, 4000)))

SET @i = PatIndex('%[a-z][^0-9A-Za-z]%' COLLATE Latin1_General_BIN, @cWork)
WHILE 0 < @i
   BEGIN
      SET @cWork = Left(@cWork, @i - 1) + Upper(SubString(@cWork, @i, 1)) + SubString(@cWork, @i + 1, 4000)
      SET @i = PatIndex('%[a-z][^0-9A-Za-z]%' COLLATE Latin1_General_BIN, @cWork)
   END

RETURN Reverse(@cWork)
END
GO

DECLARE @t TABLE (c NVARCHAR(4000))

INSERT INTO @t (c) VALUES ('THIS IS A TEST, it is only a test')
INSERT INTO @t (c) SELECT c FROM @t
INSERT INTO @t (c) SELECT c FROM @t
INSERT INTO @t (c) SELECT c FROM @t
INSERT INTO @t (c) SELECT c FROM @t
INSERT INTO @t (c) SELECT c FROM @t
INSERT INTO @t (c) SELECT c FROM @t
INSERT INTO @t (c) SELECT c FROM @t
INSERT INTO @t (c) SELECT c FROM @t
INSERT INTO @t (c) SELECT c FROM @t
INSERT INTO @t (c) SELECT c FROM @t

SELECT dbo.fCapFirst(c) FROM @t

The whole thing runs on my machine in under a second, for a thousand row test!

-PatP

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-07-02 : 18:38:56

I might have missed it but I don't think Tracy asked to convert to lowercase too. Loosely based on Wanderers script, using Pat's data, this does about 10,000 rows/second on my machine.


CREATE FUNCTION dbo.fCapFirst(@input NVARCHAR(4000)) RETURNS NVARCHAR(4000)
AS 
BEGIN
DECLARE @position INT
WHILE IsNull(@position,Len(@input)) > 1
	SELECT @input = Stuff(@input,IsNull(@position,1),1,upper(substring(@input,IsNull(@position,1),1))), 
	       @position = charindex(' ',@input,IsNull(@position,1)+1) + 1
RETURN (@input)
END

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-07-02 : 19:49:06

It just depends on what Tracey is really trying to do.... the link I provided was for capitalizing every beginning of a word, and attempting the difficult names as well.

For just after a space it could obviously be trimmed down...

Corey

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-07-03 : 00:14:24

I just tested my last suggestion and it fails when there are multiple spaces. It should have been

CREATE FUNCTION dbo.fCapFirst(@input NVARCHAR(4000)) RETURNS NVARCHAR(4000)
AS
BEGIN
DECLARE @position INT
WHILE IsNull(@position,Len(@input)) > 1
SELECT @input = Stuff(@input,IsNull(@position,1),1,upper(substring(@input,IsNull(@position,1),1))),
@position = charindex(' ',@input,IsNull(@position,1)) + 1
RETURN (@input)
END

Pat's version works fine.

BTW: I would say don't convert non-first letters to lowercase in the function. Leave them as they are and if you want them to be lowercase you can invoke the function with dbo.fCapFirst(Lower(Column))

Tracey
Starting Member

40 Posts

Posted - 2004-07-04 : 02:01:06

thank you very much guys for your response and sorry for late reply.
you all very helpful.

TY, C U all soon

Tracey

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources