sql如何获取字符串中的指定字符

发布网友发布时间：2022-04-22 07:32

我来回答

共4个回答

热心网友时间：2022-04-08 11:03

1、创建测试表，

create table test_int(value varchar2(50));

2、插入测试数据

insert into test_int values('asdfdsf?d=123&ewv');

insert into test_int values('qwer?d=234');

insert into test_int values('asfdwe?d=234&dhewu');

insert into test_int values('adfasf?d=25&reuw');

insert into test_int values('afhkashf');

commit;

3、查询表中全量数据，select t.*, rowid from test_int t;

4、编写sql，获取字符串中的指定字符;

select t.*,

case

when instr(value, 'd=') = 0 then

null

else

substr(value,

instr(value, 'd='),

case

when instr(value, '&') = 0 then

else

instr(value, '&') - instr(value, 'd=')

end)

end a

from test_int t;

热心网友时间：2022-04-08 12:21

用函数实现会比较方便，如果是SQL Server的话，可以创建如下函数:

create function dbo.get01 (@s nvarchar(1000))
returns nvarchar(1000)
as
begin
declare @idx int, @t nvarchar(1000)
set @idx = charindex('?d=', @s)
if @idx < 1
begin
  set @idx = charindex('&d=', @s)
end
if @idx > 0
begin
  set @t = right(@s, len(@s) - @idx)
  set @idx = charindex('&', @t)
  if @idx > 0
  begin
   set @t = left(@t, @idx - 1)
  end
end
return @t
end
go

调用示例:

select s.txt, dbo.get01(s.txt) as val
from (
select 'adfasf?d=25&reuw' as txt
union
select 'sparetest?d=123&ewv'
union
select 'afhkashf'
union
select 'afdafda?b=c029&d=456'
) s

结果:

热心网友时间：2022-04-08 13:56

select substring(字段,0,charindex('.',字段,0)+3) FROM 表测试过的~追问这个根本就不能用

热心网友时间：2022-04-08 15:47

SQL> insert into ckt1 values('asfdwe?d=234&dhewu');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from ckt1;

STR
--------------------------------------------------------------------------------
asfdwe?d=234&dhewu

SQL> select instr(str,'d=') loc from ckt1;

LOC
----------
8

SQL> select instr(str,'d=') starloc,instr(str,'&') endloc from ckt1;

STARLOC ENDLOC
---------- ----------
8 13

SQL> select substr(str,instr(str,'d='),instr(str,'&')) newstr from ckt1;

NEWSTR
--------------------------------------------------------------------------------
d=234&dhewu

SQL> select substr(str,instr(str,'d='),instr(str,'&')-instr(str,'d=')) newstr from ckt1;

NEWSTR
--------------------------------------------------------------------------------
d=234

这样应该可以