Removing "The" from the beginning of strings.

Question

Post reply

Removing "The" from the beginning of strings.

Jiten Bhagat

Grasshopper

Points: 14
More actions
September 18, 2006 at 10:56 am

#114576

Hi,
I'd be very grateful if someone could help me out with what seems to be a very simple requirement.
Basically, I need to remove the word 'The' from the beginning of any VARCHAR entries in a table in my database.
So for example, "The Lion, The Witch and The Wardrobe" should become "Lion, The Witch and The Wardrobe" [long story why].
Thanks,
Jiten

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Sreejith Sreedharan SSCertifiable Points: 5403 More actions · Answer 1

Try this:

SELECT ltrim(rtrim(Right(ColumnName,len(ColumnName)-3)))

FROM TableName

WHERE CHARINDEX('The', ColumnName) = 1

If this is what you want then change Select to Update:

Update TableName set ColumnName= ltrim(rtrim(Right(ColumnName,len(ColumnName)-3)))

WHERE CHARINDEX('The', ColumnName) = 1

Thanks

Sreejith

Cory E. SSCertifiable Points: 7076 More actions · Answer 2

Here is a pretty simple (and quick) option.

===============================================

declare

@string varchar(50)

select

@string = 'The Lion, The Witch, The wardrobe'

select

case

when substring(@string, 1, 4) = 'The '

then

substring(@string, 5, len(@string))

else

@string

end

select

@string = 'Curious George Rides a bike'

select

case

when substring(@string, 1, 4) = 'The '

then

substring(@string, 5, len(@string))

else

@string

end

=================================================

Results:

--------------------------------------------------

Lion, The Witch, The wardrobe

(1 row(s) affected)

--------------------------------------------------

Curious George Rides a bike

(1 row(s) affected)

-- Cory

Gopi Muluka SSCarpal Tunnel Points: 4594 More actions · Answer 3

small variation

CREATE

TABLE #TableName (ColumnName VARCHAR(100))

INSERT

#TableName VALUES(' The Lion, The Witch, The wardrobe')

INSERT

#TableName VALUES('The Lion, The Witch, The wardrobe ')

INSERT

#TableName VALUES('The Lion, The Witch , The wardrobe )

INSERT

#TableName VALUES('The Lion, The Witch, The wardrobe')

SELECT

right(

ltrim(rtrim(ColumnName)),len(ltrim(rtrim(ColumnName)))-3)

FROM

TableName

drop

table #TableName

Jiten Bhagat Grasshopper Points: 14 More actions · Answer 4

Thanks for the quick response guys.

I had *just* found a way before the suggestions. I used the following:

update Table

set ColumnName = SUBSTRING(ColumnName , 5, (LEN(ColumnName)-4))

where PrimaryKeyColumn in

(

select t.PrimaryKeyColumn

from Table t

where t.ColumnName like 'the %'

)

I'm sure this is probably not the most efficient method but it's only a one-off so guess it's okay.

Cheers again!

Jiten

Gopi Muluka SSCarpal Tunnel Points: 4594 More actions · Answer 5

Always use trim functions to avoid unpredicted results while dealing with Character Datatypes

Sergiy SSC Guru Points: 110209 More actions · Answer 6

Do not overcomplicate your queries.

This will be fine:

update Table

set ColumnName = SUBSTRING(ColumnName , 5, (LEN(ColumnName)-4))

where ColumnName like 'the %'

_____________
Code for TallyGenerator

Jiten Bhagat Grasshopper Points: 14 More actions · Answer 7

Yeah I see what you mean.

The only reason I used IN is because I actually had an INNER JOIN (I was testing for a certain condition using another related Table). When simplifying the query I used I forgot that I could 'collapse' the IN.

Cheers,

Jiten

Sergiy SSC Guru Points: 110209 More actions · Answer 8

You do not need IN even for INNER JOIN query:

update T

set ColumnName = SUBSTRING(T.ColumnName , 5, (LEN(T.ColumnName)-4))

FROM Table T

INNER JOIN ....

where T.ColumnName like 'the %'

_____________
Code for TallyGenerator

Jiten Bhagat Grasshopper Points: 14 More actions · Answer 9

Jiten Bhagat

Grasshopper

Points: 14

September 19, 2006 at 9:46 am

#661296

Oh right. I didn't know that. Thanks!!