CODEWITHSUNDEEP
sqlsql-server
Brad Germain
Brad Germain

Reputation: 597

SQL query conditioned by error

I have this query:

select "ID" =
CASE
    WHEN LEN(LEFT(ID, (charindex('.', ID)-1))) > 1 THEN LEFT(ID, (charindex('.', ID)-1))
    ELSE ID
END
From table
where tableID = '111'

The ID is something like AA11.1 or BB22 some with a period and some without. I'm wanting to truncate all characters after the period, but in the case where there is no period it errors. I want to keep what is there for an ID without a period.

So for AA11.1 I want to return AA11 and for BB22 I want to return BB22.

Any suggestions?

Upvotes: 0

Views: 71

Answers (4)

majidarif
majidarif

Reputation: 20035

A simpler query will be

SELECT IF('AA11.1' LIKE '%.%', LEFT('AA11.1', LOCATE('.','AA11.1')-1), 'AA11.1') AS tmp

so for you it will be

SELECT IF(ID LIKE '%.%', LEFT(ID, LOCATE('.',ID)-1), ID)
From table
where tableID = '111'

What it does is

  • Check if ID contains a .
  • If yes then it uses LEFT.
  • If not then it uses the ID itself.

As I missed that the TS asked for sql-server query, If anyone is looking for such query on MySQL the above example should work. As for sql-server the other answers and the modified query of the TS on the comment of this answer should also help. Good luck.

Upvotes: 1

xbb
xbb

Reputation: 2163

Try this: 

select ID =  
CASE WHEN charindex('.', ID) > 1 THEN LEFT(ID, (charindex('.', ID)-1))
    ELSE ID
END

Upvotes: 2

albe
albe

Reputation: 551

Try:

CASE
    WHEN charindex('.', ID) > 1 THEN LEFT(ID, (charindex('.', ID)-1))
    ELSE ID
END

Upvotes: 3

user275683
user275683

Reputation:

You I would do it as follows.

DECLARE @test VARCHAR(10) = 'AA110'
SELECT LEFT(@test, CASE WHEN CHARINDEX('.', @test) = 0 THEN LEN(@test)
                        ELSE CHARINDEX('.', @test) - 1
                   END);


SET @test = 'AA110.12'
SELECT LEFT(@test, CASE WHEN CHARINDEX('.', @test) = 0 THEN LEN(@test)
                        ELSE CHARINDEX('.', @test) - 1
                   END);

Upvotes: 1

Related Questions