Remove first 3 characters if 4th is a space

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)

Remove first 3 characters if 4th is a space

Author

Topic

Looper
Yak Posting Veteran

68 Posts

Posted - 2007-01-11 : 06:25:35

Hi

I am trying to write a script that will check a field within a table and will remove the first 3 characters if the 4th character is a space. Can anyone help with this?

Thanks

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-01-11 : 06:32:38

[code]select ltrim(substring(x,4,len(x))) from
(select 'aaaa' as x union all
select 'abc bb' union all
select 'xyzpqr' ) t
where x like '___ %'[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-11 : 06:45:07

Why would you only want to remove the first 3 characters, if the fourth is a space?
That would keep the space first in the string...

'abc Peso' would be ' Peso'

Peter Larsson
Helsingborg, Sweden

Looper
Yak Posting Veteran

68 Posts

Posted - 2007-01-11 : 06:52:26

Good point. What I meant to say is remove first 4 characters if the 4th is a space

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-11 : 07:11:42

Good.
Harsh has a nice suggestion for you.

Peter Larsson
Helsingborg, Sweden

p_bohra
Starting Member

1 Post

Posted - 2007-01-13 : 12:36:02

Try this if it can help you.

create table tmp1(
ename varchar(10))

Insert into tmp1 values ('Pramod')
Insert into tmp1 values ('Ram Kumar')

select substring(ename,5, len(ename)) from tmp1 where substring(ename,4,1)=' '
union
select ename from tmp1 where substring(ename,4,1)<> ' '

Alan Schofield
Starting Member

23 Posts

Posted - 2007-01-16 : 11:48:46

I may have missed the point but would this not be simpler.
It assumes you want to remove the 1st 4 chars and not replace them by spaces.

update MyTable
set MyTextField= substring(MyTextField,5,len(MyTextField))
where substring(MyTextField, 4,1) = ' '

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-16 : 12:10:40

Maybe simpler, ut probably not using any existing index on the ename column, like in Harsh's suggestion.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources