Can't seem to resolve use of LEFT or CASE

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 2008 Forums

Transact-SQL (2008)

Can't seem to resolve use of LEFT or CASE

Author

Topic

texassynergy
Starting Member

26 Posts

Posted - 2011-06-02 : 17:39:24

I have an issue where I am trying to grab the leftmost part of a field when the field has a '-' character in it. I have followed several threads and when I implement the solutions, I still get errors. Here is one of them I am using.

LEFT(LTFLS.STOCK_LOCATION, CHARINDEX('-', LTFLS.STOCK_LOCATION)-1)

When I do, I get the following error message:
Msg 537, Level 16, State 2, Line 1
Invalid length parameter passed to the LEFT or SUBSTRING function.

However, the sytax is identical to several threads I have reviewed.

I have also tried using:

CAST(CASE WHEN CharIndex('-',LTFLS.STOCK_LOCATION)=0 THEN LTFLS.STOCK_LOCATION ELSE LEFT(LTFLS.STOCK_LOCATION,CharIndex('-',LTFLS.STOCK_LOCATION)-1)
  AS VARCHAR)

CAST(CASE WHEN CharIndex('-',LTFLS.STOCK_LOCATION)=0 THEN LTFLS.STOCK_LOCATION ELSE LEFT(LTFLS.STOCK_LOCATION,CharIndex('-',LTFLS.STOCK_LOCATION)-1))

Both of them give me an Incorrect syntax error near ) or near the AS. I have double checked and each close parenth has an open parenth.

Any help would be greatly appreciated.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-06-02 : 17:58:01

Not 100% sure as some of those should work. Here is a sample:

DECLARE @Foo TABLE(Val VARCHAR(100) )

INSERT @Foo (Val)
VALUES ('abcdefghijk'), ('abcde-ghijk'), (NULL)

SELECT
    CASE 
        WHEN CHARINDEX('-', Val)=0 
            THEN Val 
        ELSE 
            LEFT(Val, CHARINDEX('-', Val) - 1)
    END
FROM @Foo

texassynergy
Starting Member

26 Posts

Posted - 2011-06-02 : 18:09:48

Thanks Lamprey,

I just needed to use the END statement for the case. Once I did that, then it worked. Thanks for the insight. I missed that earlier.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources