SQL查询用于分隔的字符串

huangapple go评论71阅读模式
英文:

SQL query for delimited string

问题

我有一个表格中的字符串列,它的长度可能不同,并以 / 分隔,如下例所示。我尝试删除最后一个 / 标志之后的任何字符,包括最后一个 / 标志。任何想法将不胜感激,我已经尝试了 left、charindex 等选项,但没有成功。

删除的原因是为了能够与其他表格进行连接以查找值。

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

期望的输出:
Sh/cm;
Sh/nj/mdr;
Sh/kl/uhj/tyu bh

我已经尝试了 left、charindex 等选项,但没有成功。

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

期望的输出:
Sh/cm;
Sh/nj/mdr;
Sh/kl/uhj/tyu bh

英文:

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

答案1

得分: 0

以下是代码的中文翻译:

创建表格 test(id int,keys varchar(100));
 test 插入值 
 (1,'Sh/cm/rq')
,(2,'Sh/nj/mdr/wep')
,(3,'Sh/kl/uhj/tyu bh/red')
;
选择 *
  , charindex('/',keys)>0 时,
     反转(
        子串(反转(keys),charindex('/',反转(keys))+1,100)
      )
  否则 keys
  作为 newkeys
 test

请注意,代码中的特殊字符(如''')已被恢复为原始的单引号。

英文:

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

huangapple
  • 本文由 发表于 2023年4月13日 20:55:59
  • 转载请务必保留本文链接:https://go.coder-hub.com/76005701.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定