CODEWITHSUNDEEP
mysqlsqlsql-server
Takeshi Tawarada
Takeshi Tawarada

Reputation: 113

SQL Take off last character if a certain one exists in a string

I have a column with few different ID's

abc_1234
abc_2345
bcd_3456/
cde_4567/

And I want a new column that takes off the / if it exists

abc_1234
abc_2345
bcd_3456
cde_4567

I know I'll be using a combination of IF/THEN, LEFT, and LEN, but I don't know the syntax. Help is appreciated! Thanks!

Upvotes: 6

Views: 3654

Answers (4)

Ray Krungkaew
Ray Krungkaew

Reputation: 6965

For SQLServer

declare @tbl table
(
    c1 nvarchar(9)
)
insert into @tbl values ('abc_1234')
insert into @tbl values ('abc_2345')
insert into @tbl values ('abc_3456/')
insert into @tbl values ('abc_4567/')

select  IIF(RIGHT(c1, 1) = '/', LEFT(C1, LEN(C1)-1), C1)
from    @tbl

Result
abc_1234
abc_2345
abc_3456
abc_4567

Upvotes: 0

potashin
potashin

Reputation: 44581

(In case your are using SQL Server RDBMS)

You can try the following combination of right and left:

case when right(col, 1) = '/' then left(col, len(col)-1) else col end

SQLFiddle

(In case your are using MySQL RDBMS)

trim(trailing '/' from col);

SQLFiddle

Upvotes: 8

Thorsten Kettner
Thorsten Kettner

Reputation: 94884

You would usually simply trim the string:

  • Oracle: select rtrim(col, '/') from ...
  • MySQL and PostgreSQL: select trim(trailing '/' from col) from ...

but not all DBMS offer this. SQL Server for instance doesn't.

Upvotes: 0

Nate S.
Nate S.

Reputation: 1157

If your using SQL Server try this

SELECT REPLACE(col,'/','')

Replace (Transact-SQL)

Upvotes: 2

Related Questions