在一个表里如何用SQL得到。
SName ID
123\1xx\2aa\4\ 101
123\2xx\2a1\304\ 102
123\3xx\2a3\355\ 103
123\4xx\2a55\320\ 104
123\5xx\2a2\302\ 105
123\1xx\2aa\4\899\3 101
123\2xx\2a1\304\ 102
123\3xx\2a3\355\ 103
问题： 获得SName第二“\”以前面的内容
------解决方案--------------------
select substring('123\1xx\2aa\4\',1,(charindex('\','123\1xx\2aa\4\',(charindex('\','123\1xx\2aa\4\')+1))-1))
--------------
123\1xx
(1 行受影响)
------解决方案--------------------
--轉老大
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_GetStr]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_GetStr]
GO
--分段截取函数
CREATE FUNCTION dbo.f_GetStr(
@s varchar(8000), --包含多个数据项的字符串
@pos int, --要获取的数据项的位置
@split varchar(10) --数据分隔符
)RETURNS varchar(100)
AS
BEGIN
IF @s IS NULL RETURN(NULL)
DECLARE @splitlen int
SELECT @splitlen=LEN(@split+'a')-2
WHILE @pos>1 AND CHARINDEX(@split,@s+@split)>0
SELECT @pos=@pos-1,
@s=STUFF(@s,1,CHARINDEX(@split,@s+@split)+@splitlen,'')
RETURN(ISNULL(LEFT(@s,CHARINDEX(@split,@s+@split)-1),''))
END
GO
select dbo.f_GetStr('2∮11∮10∮09∮10∮13∮786∮91.93∮69∮100.00 ',7,'∮')
/*
----------------------------------------------------------------------------------------------------
786
(1 個資料列受到影響)
贴一下算了 就不写了
------解决方案--------------------
----------------------------------------------------------------
-- Author :DBA_HuangZJ(发粪涂墙)
-- Date :2014-03-04 15:20:54
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
-- Apr 2 2010 15:48:46
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([SName] nvarchar(38),[ID] int)
insert [huang]
select '123\1xx\2aa\4\',101 union all
select '123\2xx\2a1\304\',102 union all
select '123\3xx\2a3\355\',103 union all
select '123\4xx\2a55\320\',104 union all
select '123\5xx\2a2\302\',105 union all
select '123\1xx\2aa\4\899\3',101 union all
select '123\2xx\2a1\304\',102 union all
select '123\3xx\2a3\355\',103
--------------生成数据--------------------------
select SUBSTRING(sname,1,PATINDEX('%\%',sname))+
SUBSTRING(SUBSTRING(sname,LEN(SUBSTRING(sname,1,PATINDEX('%\%',sname)))+1,LEN(sname)),1,PATINDEX('%\%',SUBSTRING(sname,LEN(SUBSTRING(sname,1,PATINDEX('%\%',sname)))+1,LEN(sname)))-1),
ID
from [huang]
----------------结果----------------------------
/*
---------------------------------------------------------------------------- -----------
123\1xx 101
123\2xx 102
123\3xx 103