SQL IF STATEMENT - 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

General SQL Server Forums

New to SQL Server Programming

SQL IF STATEMENT

Author

Topic

dotman
Starting Member

5 Posts

Posted - 2008-10-29 : 11:56:40

Hi,

I have a field called PLAN_REF, if the LEN of PLAN_REF is = 8 then I want to LTRIM(PLAN_REF,7) otherwise I want to show the full PLAN_REF

Used to doing this in access and excel however can't get it to work in sql!

Any help appreciated

dotman

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-10-29 : 11:58:40

You probably want CASE instead


SELECT
    CASE LEN(PLAN_REF)
        WHEN 8 THEN LTRIM(PLAN_REF,7)
        ELSE PLAN_REF
    END
FROM
    <yourTable>

-------------
Charlie

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-29 : 12:01:43

quote:
Originally posted by Transact Charlie

You probably want CASE instead

SELECT
CASE LEN(PLAN_REF)
WHEN 8 THEN LEFT~~LTRIM~~(PLAN_REF,7)
ELSE PLAN_REF
END
FROM
<yourTable>

that should be LEFT not LTRIM

-------------
Charlie

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-10-29 : 12:13:40

Well done Visakh16. You spotted my deliberate mistake. top marks!

Dotman: Visakh16 is right of course you probably do want LEFT(7). However I just copied what you'd written.

Maybe if that doesn't do as you would expect you should post some sample data?

Regards,

-------------
Charlie

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-29 : 12:16:27

quote:
Originally posted by Transact Charlie

Well done Visakh16. You spotted my deliberate mistake. top marks!

Dotman: Visakh16 is right of course you probably do want LEFT(7). However I just copied what you'd written.

Maybe if that doesn't do as you would expect you should post some sample data?

Regards,

-------------
Charlie

Thanks

dotman
Starting Member

5 Posts

Posted - 2008-10-29 : 12:40:44

Thanks both!
It does the job perfectly.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-29 : 12:41:50

cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources