How to format an output? - 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)

How to format an output?

Author

Topic

polygonSQL
Starting Member

8 Posts

Posted - 2005-01-28 : 06:22:24

Is their any command to make a row that = 'GREEN BAY' = 'Green Bay'?
I've made this script for all my cities that are one word:

-----------------------
update patients
set patcity = (
select top 1 left(patcity, 1) + right(patcity, (datalength(patcity) - 1))
from patients S where S.patid = P.patid
)
from patients P
where patcity not like '% %'

-----------------------
but how would I do it for cities that have 2 or three words?

select * from end_user
where clue > 0
GO
( 0 rows returned )

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-01-28 : 07:05:13

Try this

Declare @t table(n varchar(100))
insert into @t values('GREEN BAY')
Select left(n,1)+lower(substring(n,2,charindex(' ',n)-1))+ substring(n,charindex(' ',n),2)
+lower(substring(n,charindex(' ',n)+2,len(n))) from @t

Madhivanan

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-01-28 : 08:13:43

Read through this:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=40338

Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

polygonSQL
Starting Member

8 Posts

Posted - 2005-01-28 : 18:46:21

quote:
Originally posted by madhivanan

Try this

Declare @t table(n varchar(100))
insert into @t values('GREEN BAY')
Select left(n,1)+lower(substring(n,2,charindex(' ',n)-1))+ substring(n,charindex(' ',n),2)
+lower(substring(n,charindex(' ',n)+2,len(n))) from @t

Madhivanan

Genius pure genius, I haven't had the time to break this down, but I tested it and I think it will work for my purpose.

polygonSQL
Starting Member

8 Posts

Posted - 2005-01-28 : 18:51:16

quote:
Originally posted by Seventhnight

Read through this:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=40338

Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

OOoooo oooh ooh, it's like crack err I mean a candy store and I'm in it. Johnny 5 -- Input, input, input!

-----------------------
select * from end_user
where clue > 0
GO
( 0 rows returned )

polygonSQL
Starting Member

8 Posts

Posted - 2005-01-29 : 19:44:02

Thanks Seventhnight!

fyi this is the script I used
I changed len to datalength because if the field read 'GREEN BAY ' there would be problems

Declare @table table (oldcity varchar(1000), newcity varchar(1000), pos int default(-1))
Insert Into @table (oldcity)
Select patcity from patients

Update @table Set newcity = upper(left(oldcity,1)) + right(lower(oldcity),datalength(oldcity)-1) From @table

Declare @pos int

while exists(Select * From @table Where pos<>0)
Begin
Update @Table
Set
newcity = case when pos>0 then left(newcity,pos) + upper(substring(newcity,pos+1,1)) + right(newcity,datalength(newcity)-pos-1) else newcity end,
pos = case when patindex('%[^a-z][a-z]%',right(newcity,datalength(newcity)-pos))>0 then
patindex('%[^a-z][a-z]%',right(newcity,datalength(newcity)-pos)) + case when pos=-1 then 0 else pos end
else 0 end
From @Table
Where pos<>0
End

Update patients
Set patcity = (select top 1 newcity from @table where oldcity = patients.patcity)

-----------------------
select * from end_user
where clue > 0
GO
( 0 rows returned )

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources