Search and Replace 'spaces' at the begining of....

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)

Search and Replace 'spaces' at the begining of....

Author

Topic

ChrisHorsley
Starting Member

7 Posts

Posted - 2004-08-03 : 07:41:16

I have a quarterly table I need to import that has a space before each name in the CustomerName field. Does anyone know how I can search and remove the space (if it exsists) before the start of the name?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-03 : 07:50:10

this would probably work.
CASE WHEN CHARINDEX(' ', columnName) = 1
THEN SUBSTRING(columnName, 2, Len(columnName)-1)
END

if your columnName starts with ' ' then it will extract the string from 2nd char on.

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

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-08-03 : 07:50:22

UPDATE table
SET name = LTRIM(name)

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-03 : 07:51:57

of course you could just use LTrim :))

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

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2004-08-03 : 07:54:13

I like the question "how do I replace multiple adjacent spaces with just one space?" better.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-03 : 07:56:50

you now what i sometimes miss i sql... an LTrim (RTrim) where you can specify the character you want to trim...
or does it already exist?

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

JasonGoff
Posting Yak Master

158 Posts

Posted - 2004-08-03 : 08:26:38

How about...


declare @mystring varchar(100)
declare @char char(1)
select @mystring='aardvark'
select @char='a'

--replace spaces with a marker
SELECT @mystring=REPLACE(@mystring,' ','~#~')
--replace char with a space
SELECT @mystring=REPLACE(@mystring,@char,' ')
--trim it
SELECT @mystring=RTRIM(LTRIM(@mystring))
--put the characters back
SELECT @mystring=REPLACE(@mystring,' ',@char)
--put the spaces back
SELECT @mystring=REPLACE(@mystring,'~#~',' ')
--show result
SELECT @mystring

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2004-08-03 : 08:44:38

Think I'd prefer something like this, which won't go wrong with string length problems, pathologically strange strings, etc.

SUBSTRING(@mystring, CHARINDEX(LEFT(REPLACE(@mystring, @char, ''),1), @mystring), LEN(@mystring))

BTW, This is only the ltrim

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-03 : 08:51:21

that' cool and all... but wouldn't it be great if you could just say LTRIM('.') and voila...
maybe in SQL server 2005 version.

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

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-08-03 : 09:01:59

Nope....nothing like that in 2005. You could make a custom function for it. Oh wait..that's what all those examples were. :)

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources