sql如何获取字符串中的指定字符 sql获取指定字符串之间的字符串
sql\u8bed\u53e5\uff0c\u5982\u4f55\u622a\u53d6\u6307\u5b9a\u5b57\u6bb5\u67d0\u4e00\u4e2a\u5b57\u7b26\u51fa\u73b0\u540e\u7684\u540e\u9762\u7684\u5b57\u7b26\u4e32\u5417\u5de5\u5177/\u6750\u6599\uff1aManagement Studio\u3002
1\u3001\u9996\u5148\u5728\u684c\u9762\u4e0a\uff0c\u70b9\u51fb\u201cManagement Studio\u201d\u56fe\u6807\u3002
2\u3001\u4e4b\u540e\u5728\u8be5\u754c\u9762\u4e2d\uff0c\u70b9\u51fb\u5de6\u4e0a\u89d2\u201c\u65b0\u5efa\u67e5\u8be2\u201d\u9009\u9879\u3002
3\u3001\u63a5\u7740\u5728\u8be5\u754c\u9762\u4e2d\uff0c\u8f93\u5165\u622a\u53d6\u6307\u5b9a\u5b57\u6bb5\u67d0\u4e00\u4e2a\u5b57\u7b26\u51fa\u73b0\u540e\u7684\u540e\u9762\u7684\u5b57\u7b26\u4e32\u7684sql\u8bed\u53e5\u201c
select substring(name,charindex(',',name)+1,len(name)-charindex(',',name)) from test2;\u201d\u3002
4\u3001\u7136\u540e\u5728\u8be5\u754c\u9762\u4e2d\uff0c\u70b9\u51fb\u5de6\u4e0a\u65b9\u201c\u6267\u884c\u201d\u6309\u94ae\u3002
5\u3001\u6700\u540e\u5728\u8be5\u754c\u9762\u4e2d\uff0c\u663e\u793a\u622a\u53d6\u6307\u5b9a\u5b57\u6bb5\u67d0\u4e00\u4e2a\u5b57\u7b26\u51fa\u73b0\u540e\u7684\u540e\u9762\u7684\u5b57\u7b26\u4e32\u6210\u529f\u3002
\u53ef\u4ee5\u8fd9\u6837\uff0c\u5982\u4e3a\u5b57\u6bb5\u5219\u7528\u5b57\u6bb5\u540d\u66ff\u6362@str\u5373\u53ef
declare @str varchar(50)
set @str = '2012-07:\u7535\u8868(00300):\u5355\u4ef7[\uffe51.30]*\u7528\u91cf[9000.00]';
select substring(@str,charindex('\uffe5',@str,1)+ 1,charindex(']',@str,charindex('\uffe5',@str,1)) - charindex('\uffe5',@str,1) -1) as \u5355\u4ef7,
substring(@str,charindex('\u7528\u91cf[',@str,1)+ 3,len(@str) - charindex('\u7528\u91cf[',@str,1) -3) as \u7528\u91cf;
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
10
else
instr(value, '&') - instr(value, 'd=')
end)
end a
from test_int t;
用函数实现会比较方便，如果是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
结果:
select substring(字段,0,charindex('.',字段,0)+3) FROM 表 测试过的~
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
这样应该可以
绛旓細鍙敤charindex鍑芥暟銆傚锛氭煡鎵瀛楃涓蹭腑鈥滀綘濂解濈殑浣嶇疆 鎵ц锛歴elect charindex('浣犲ソ','2432浣犲ソdsfasdf')缁撴灉锛氱粨璁猴細鈥滀綘濂解濅腑鐨勨滀綘鈥濈殑璧峰浣嶇疆鏄5锛屾墍浠ヨ繖鏍峰氨鑳藉垽鏂嚭鎸囧畾瀛楃鐨勪綅缃簡銆
绛旓細sql鎬庢牱鎻愬彇绗竴涓瓧绗︾殑鏂规硶鐢ㄥ埌鐨勬槸substring() 鏂规硶鐢ㄤ簬鎻愬彇瀛楃涓蹭腑浠嬩簬涓や釜鎸囧畾涓嬫爣涔嬮棿鐨勫瓧绗︺傝繑鍥炲 涓涓柊鐨勫瓧绗︿覆锛岃瀛楃涓插煎寘鍚 stringObject 鐨勪竴涓瓙瀛楃涓诧紝鍏跺唴瀹规槸浠 start 澶勫埌 stop-1 澶勭殑鎵鏈夊瓧绗︼紝鍏堕暱搴︿负 stop 鍑 start銆2.璇存槑 substring() 鏂规硶杩斿洖鐨勫瓙涓插寘鎷 start 澶...
绛旓細绫讳技鎴戞槰澶╁洖绛旇繃鐨勯棶棰樸傝璁℃濊矾锛氭妸 25锛堥渶鍖归厤鐨勬暟鎹級锛屽墧鍑烘帀銆傚墧鍑哄悗锛岃繛缁繛涓楀彿锛屾垨棣栭楀彿锛屾垨灏鹃楀彿锛岄兘璇存槑鍙惈鏈夐渶鍖归厤鐨勬暟鎹俿elect 鍏朵粬瀛楁 from (select 鍏朵粬瀛楁,replace(瀛楁,'25','') as tt from 琛ˋA)where tt like in (',%',',,','%,')鎴 where tt like ',...
绛旓細濡傛灉鏄涓浣嶏紝鍙互鐩存帴鐢╨eft()鍑芥暟 select left(book_id,1) from 琛ㄨ繖鏍峰氨鍙互浜 濡傛灉鏄埅鍙栫涓浣嶅悗鐨勫墠浜屼綅锛岃鐢╯ubstring()鍑芥暟 select substring(book_id,2,2)from 琛ㄨ繖鏍峰氨鍙互浜 鏈夐棶棰樿缁х画杩介棶
绛旓細/*** Sql Server涓埅鍙瀛楃涓茬殑甯哥敤鏂规硶 ***/--1銆丩EFT()鏂规硶---鍑芥暟璇存槑---1锛夎娉曪細LEFT(character,integer) --2锛変粙缁嶏細鍙傛暟1锛氳鎴彇鐨勫瓧绗︿覆锛屽弬鏁2锛氭埅鍙栧瓧绗︿釜鏁--3锛変娇鐢細--杩斿洖浠庡瓧绗︿覆宸﹁竟寮濮鎸囧畾涓暟鐨勫瓧绗--select LEFT('SqlServer_2008',3)--4锛夎繑鍥烇細Sql--1銆丷IGHT...
绛旓細create procedure [dbo].[sp_split_string]id varchar(4000)as begin declare @object_id nvarchar(500)declare @i INT declare @len INT declare @count int declare @nid varchar(1000)if (@id IS NULL) OR (ltrim(@id) = '')return SET @nid=''while charindex(',',@id) > 0 BEGIN...
绛旓細substring('2009/2/5',1,6)SUBSTRING (<expression>锛 <starting_ position>锛 length)杩斿洖浠瀛楃涓宸﹁竟绗瑂tarting_ position 涓瓧绗﹁捣length涓瓧绗︾殑閮ㄥ垎銆俵eft('2009/2/5',6)LEFT (<character_expression>锛 <integer_expression>)杩斿洖character_expression 宸﹁捣 integer_expression 涓瓧绗︺--- CONV...
绛旓細select substr(oid,instr(oid,'.')+1,instr(oid,'.',instr(oid,'.') + 1) - instr(oid,'.') -1 ) from tabname;
绛旓細charindex(')',select 瀛楁鍚 from 琛ㄥ悕 where id=1))-charindex('(',select 瀛楁鍚 from 琛ㄥ悕 where id=1)))灏辨槸涓や釜鍑芥暟 substring("1231",1,2) ="12" 1璧峰浣嶇疆 2鎴彇闀垮害 charindex('(','ab(cd')=3 鑾峰彇鍦ㄥ瓧娈'ab(cd' 涓橈紙鈥欏瓧绗﹀湪瀛楃涓蹭腑鐨绱㈠紩浣嶇疆 ...
绛旓細鐢╯ubstr('adfaadsfa', 2, 4)鍑芥暟锛宻ubstr()鏄鍙栧瓧绗︿覆鐨瀛愪覆鐨勫嚱鏁帮紝2琛ㄧず浠庣浜屼釜瀛楃寮濮, 4 琛ㄧず鎬诲叡鍙4涓瓧绗︺
