Easy Syntax

Question

Easy Syntax

rachel

SSCommitted

Points: 1626
More actions
May 24, 2002 at 3:33 pm

#78450

Could someone please help me with this syntax?
update #temploadpac set city_state = left(city_state, len(city_state)-3)
basically I need to set the city_state field equal to itself minus the rightmost 3 characters. I get an error that says "Invalid length parameter passed to the substring function".
Thanks!!

Viewing 13 posts - 1 through 12 (of 12 total)

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

rachel SSCommitted Points: 1626 More actions · Answer 1

Actually I need to do the opposite. I need to set city_state = to its length minus 3 characters, as opposed to setting its length equal to 3 characters.

I believe it has to do with handling 0 length strings - but I can't remember how to do that in this context...

Thanks!

sarmistead SSCommitted Points: 1671 More actions · Answer 2

You need to check the length of the city_state field before you take the leftmost chars. There is probably one record (or more) with a city_state length of 3 or less, causing the left function to fail.

Try this:

set city_state = case when len(ltrim(city_state)) > 3 then left(city_state, len(ltrim(city_state))-3) else ltrim(city_state) end

Steve Armistead,

Database Administration

Panther Systems Northwest, Inc.

Vancouver, WA

Steve

rachel SSCommitted Points: 1626 More actions · Answer 3

Thanks, thats what I was trying to remember! I am getting an error now b/c I have a where clause after the end of the case statement - the error says incorrect syntax near the 'where' clause - does the case statement have to be "ended" somehow before using the where clause?

Thanks!

rachel SSCommitted Points: 1626 More actions · Answer 4

What I have is ....

update #temploadpac set city_state = CASE WHEN Len(CityState) < 4 Then CityState

ELSE Left(CityState, Len(CityState)-3) where city_state is not null

I'm getting a syntax error on the "where" clause. Thanks

rachel SSCommitted Points: 1626 More actions · Answer 5

I tried adding the "end, " to the code, but I am still getting the same error. Any other ideas? Thanks!

rachel SSCommitted Points: 1626 More actions · Answer 6

I don't think I need a (from tablename) statement in an update clause... 🙂

sarmistead SSCommitted Points: 1671 More actions · Answer 7

sarmistead

SSCommitted

Points: 1671

May 24, 2002 at 4:22 pm

#429751

remove the final comma after END

Steve

rachel SSCommitted Points: 1626 More actions · Answer 8

having the comma in or out both give me errors, two different errors, but errors.

We are soo close!! Thank you for your help!!

Andy Jones, DBA Hall of Fame Points: 3641 More actions · Answer 9

Remove the comma after the end and have a consistent column name i.e. city_state not CityState see below:-

update #temploadpac

set city_state =

CASE WHEN Len(city_state) < 4

Then city_state

ELSE Left(city_state , Len(city_state)-3)

END

where city_state is not null

Regards,

Andy Jones

andyj93@hotmail.com

.

rachel SSCommitted Points: 1626 More actions · Answer 10

Thank you for pointing that out - I had actually not corrected the syntax on my field name. I will give that a try and see if it corrects the error I was getting.

rachel SSCommitted Points: 1626 More actions · Answer 11

Thank you everyone. Once I re-wrote the fieldnames so they all matched again, everything works - no comma after 'end'.

Thanks to everyone for their help!

shrinivaskundarapu SSC Veteran Points: 272 More actions · Answer 12

Update #temploadpack

SET city_state=

WHEN LEN(city_state) > 3 then

LEFT(city_state,(LEN(city_state)-3))

WHEN LEN(city_state) < 3 then

city_state

END

from #temploadpack

where city_state is not null

Shrinivas L.K.