Search code examples
sqldelimited

SQL query for delimited string

I have string column in a table, its length can be different and delimited by /, examples below. I am trying to remove any last characters after last / sign, including last / sign. Any ideas would be appreciated, I haves tried left, charindex, etc options but no luck. Reason to remove is so that I can do join with other table to find values.

Source:
Sh/cm/rq;
Sh/nj/mdr/wep;
Sh/kl/uhj/tyu bh/red

Looking to output
Sh/cm;
Sh/nj/mdr;
Sh/kl/uhj/tyu bh

I haves tried left, charindex, etc options but no luck.

Source:
Sh/cm/rq;
Sh/nj/mdr/wep;
Sh/kl/uhj/tyu bh/red

Looking to output
Sh/cm;
Sh/nj/mdr;
Sh/kl/uhj/tyu bh
Solution

  • Try this

    create table test(id int,keys varchar(100));
insert into test values 
 (1,'Sh/cm/rq')
,(2,'Sh/nj/mdr/wep')
,(3,'Sh/kl/uhj/tyu bh/red')
;
select *
  ,case when charindex('/',keys)>0 then
     reverse(
        substring(reverse(keys),charindex('/',reverse(keys))+1,100)
      )
  else keys
  end newkeys
from test