查找子字符串/charindex T-sql



我有以下变量。

DECLARE @TestConnectionString varchar(255) = 'Data Source=123.45.67.890;User ID=TestUser;Password=TestPassword;Initial Catalog=TestCatalogName;Provider=SQLNCLI11.1;Persist Security Info=True;Auto Translate=False;'

我想从这个连接字符串中分离出每个属性的值。

我确信我必须使用SUBSTRINGCHARINDEX,但不确定如何使用。我不想硬编码每个属性的长度,因为user_id可能是"Comeonedude"

有人能告诉我如何提取其中的一些属性作为例子吗?

与此同时,我会试着看看我是否能想出什么办法。

感谢

我喜欢在TSQL中使用XML强制转换来拆分字符串。这种方法是首选的,因为它不需要到处创建字符串分割函数,而且根据我的经验,它的性能和扩展性都很好。下面是一个SQLFiddle示例。

DECLARE @TestConnectionString varchar(255) = 'Data Source=123.45.67.890;User ID=TestUser;Password=TestPassword;Initial Catalog=TestCatalogName;Provider=SQLNCLI11.1;Persist Security Info=True;Auto Translate=False;'
SELECT
     t.c.value('(property)[1]','VARCHAR(200)') AS [property]
    ,t.c.value('(value)[1]','VARCHAR(200)') AS [value]
FROM (
    SELECT CAST('<root><pair><property>' + REPLACE(REPLACE(LEFT(@TestConnectionString,LEN(@TestConnectionString)-1),';','</value></pair><pair><property>'),'=','</property><value>') + '</value></pair></root>' AS XML) AS properties_xml
) AS i
CROSS APPLY i.properties_xml.nodes('/root/pair') AS t(c)

说明:

@TestConnectionString由以下select语句格式化为XML文档:

SELECT CAST('<root><pair><property>' + REPLACE(REPLACE(LEFT(@TestConnectionString,LEN(@TestConnectionString)-1),';','</value></pair><pair><property>'),'=','</property><value>') + '</value></pair></root>' AS XML) AS properties_xml

XML字符串以<root><pair><property>开头,然后REPLACE函数用</value></pair><pair><property>替换每个分隔分号,并用</property><value>替换每个分隔等号。@TestConnectionString以分号结尾,因此LEFT函数必须首先删除分号,否则我们将在XML字符串的末尾多加一个</value></pair><pair><property>。XML字符串是通过附加</value></pair></root>来完成的,我们最终得到的是:

<root>
  <pair>
    <property>Data Source</property>
    <value>123.45.67.890</value>
  </pair>
  <pair>
    <property>User ID</property>
    <value>TestUser</value>
  </pair>
  <pair>
    <property>Password</property>
    <value>TestPassword</value>
  </pair>
  <pair>
    <property>Initial Catalog</property>
    <value>TestCatalogName</value>
  </pair>
  <pair>
    <property>Provider</property>
    <value>SQLNCLI11.1</value>
  </pair>
  <pair>
    <property>Persist Security Info</property>
    <value>True</value>
  </pair>
  <pair>
    <property>Auto Translate</property>
    <value>False</value>
  </pair>
</root>

使用CAST函数将XML字符串转换为XML数据类型。CROSS APPLY运算符可用于将XML文档的节点转换为具有行和列(别名为c)的类似于表的对象(别名为t)。

CROSS APPLY i.properties_xml.nodes('/root/pair') AS t(c)

现在我们有了一个表,其中的行表示XML文档中的每个对节点。可以从中选择此表,使用value函数为要选择的每一列分配一个数据类型。

SELECT
     t.c.value('(property)[1]','VARCHAR(200)') AS [property]
    ,t.c.value('(value)[1]','VARCHAR(200)') AS [value]

首先在";"处拆分字符串。。您可以在网上找到许多拆分功能。使用一个将其拆分成表格的方法。

以下代码来自:如何使用T-SQL使用分隔符字符拆分字符串?

 CREATE FUNCTION [dbo].[Split]
(   
 @String varchar(max)
,@Delimiter char =';' -- default value
)
RETURNS @Results table
(
 Ordinal int
,StringValue varchar(max)
)
as
begin
    set @String = isnull(@String,'')
    set @Delimiter = isnull(@Delimiter,'')
    declare
     @TempString varchar(max) = @String
    ,@Ordinal int = 0
    ,@CharIndex int = 0
    set @CharIndex = charindex(@Delimiter, @TempString)
    while @CharIndex != 0 begin     
        set @Ordinal += 1       
        insert @Results values
        (
         @Ordinal
        ,substring(@TempString, 0, @CharIndex)
        )       
        set @TempString = substring(@TempString, @CharIndex + 1, len(@TempString) - @CharIndex)     
        set @CharIndex = charindex(@Delimiter, @TempString)
    end
    if @TempString != '' begin
        set @Ordinal += 1 
        insert @Results values
        (
         @Ordinal
        ,@TempString
        )
    end
    return
end

假设顺序始终相同,则在"="处拆分每个resutl。取每个字符串的右部分('='之后剩余字符串的长度)。。

瞧,每一处房产都有其价值。

--编辑:使用上面的拆分功能:

DECLARE @TestConnectionString varchar(255) = 'Data Source=123.45.67.890;User ID=TestUser;Password=TestPassword;Initial Catalog=TestCatalogName;Provider=SQLNCLI11.1;Persist Security Info=True;Auto Translate=False;'
create table #result
(
property varchar(255),
Value varchar(255)
)
create table #tmp
(
Property varchar(255)
)
create table #tmp2
(
Value varchar(255)
)
insert into #tmp
select * from split(@TestConnectionString, ';')

--select * from #tmp
/* Sclaufe */
declare @id varchar(255)
DECLARE a_coursor CURSOR FOR
select property from #tmp
OPEN a_coursor;
FETCH NEXT FROM a_coursor into     @id;
WHILE @@FETCH_STATUS = 0
BEGIN
    -- select @id
    insert into #tmp2 
    select * from Split(@id, '=')
 FETCH NEXT FROM a_coursor
INTO      @id
END;
CLOSE a_coursor;
DEALLOCATE a_coursor;

select * from #tmp2
/* Sclaufe */
declare @id2 varchar(255)
declare @oldid varchar(255)
declare @count int
set @count = 1
DECLARE a_coursor CURSOR FOR
select value from #tmp2
OPEN a_coursor;
FETCH NEXT FROM a_coursor into     @id2;
WHILE @@FETCH_STATUS = 0
BEGIN
    print @id2
    if @count % 2 <> 0
    begin
        insert into #result
        select @id2, ''
        set @oldid = @id2
    end
    else
    begin
        update #result
        set Value = @id2
        where property = @oldid
    end
    set @count = @count + 1
 FETCH NEXT FROM a_coursor
INTO      @id2
END;
CLOSE a_coursor;
DEALLOCATE a_coursor;
select * from #result

 drop table #tmp
 drop table #tmp2
 drop table #result

结果将显示在#ressult表中:

╔═══════════════════════╦═════════════════╗
║       property        ║      Value      ║
╠═══════════════════════╬═════════════════╣
║ Data Source           ║ 123.45.67.890   ║
║ User ID               ║ TestUser        ║
║ Password              ║ TestPassword    ║
║ Initial Catalog       ║ TestCatalogName ║
║ Provider              ║ SQLNCLI11.1     ║
║ Persist Security Info ║ True            ║
║ Auto Translate        ║ False           ║
╚═══════════════════════╩═════════════════╝

编辑:或者您可以创建一个存储过程:

if exists (select 1 from sysobjects where name = 'getvalue2' and type = 'P')
begin
   drop procedure getvalue2
   print 'Procedure: getvalue2 deleted ...'
end
go
/*
 exec getvalue2 'Data Source=123.45.67.890;User ID=TestUser;Password=TestPassword;Initial Catalog=TestCatalogName;Provider=SQLNCLI11.1;Persist Security Info=True;Auto Translate=False;'
*/
create procedure [dbo].[getvalue2]
(  @TestConnectionString varchar(255)) 
as 
begin
    --= 'Data Source=123.45.67.890;User ID=TestUser;Password=TestPassword;Initial Catalog=TestCatalogName;Provider=SQLNCLI11.1;Persist Security Info=True;Auto Translate=False;'
    create table #result
    (
    property varchar(255),
    Value varchar(255)
    )
    create table #tmp
    (
    firstrun varchar(255)
    )
    create table #tmp2
    (
    secondrun varchar(255)
    )
    insert into #tmp
    select * from split(@TestConnectionString, ';')

    --select * from #tmp
    declare @id varchar(255)
    DECLARE a_coursor CURSOR FOR
    select firstrun from #tmp
    OPEN a_coursor;
    FETCH NEXT FROM a_coursor into     @id;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        insert into #tmp2 
        select * from Split(@id, '=')
     FETCH NEXT FROM a_coursor
    INTO      @id
    END;
    CLOSE a_coursor;
    DEALLOCATE a_coursor;
    declare @id2 varchar(255)
    declare @oldid varchar(255)
    declare @count int
    set @count = 1
    DECLARE a_coursor CURSOR FOR
    select secondrun from #tmp2
    OPEN a_coursor;
    FETCH NEXT FROM a_coursor into     @id2;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        print @id2
        if @count % 2 <> 0
        begin
            insert into #result
            select @id2, ''
            set @oldid = @id2
        end
        else
        begin
            update #result
            set Value = @id2
            where property = @oldid
        end
        set @count = @count + 1
     FETCH NEXT FROM a_coursor
    INTO      @id2
    END;
    CLOSE a_coursor;
    DEALLOCATE a_coursor;
    select * from #result
end

玩得开心,你很好=)

如果您真的想在没有硬编码数字的情况下使用SUBSTRING,可以使用以下通用方法:

DECLARE @TestConnectionString varchar(255) = 'Data Source=123.45.67.890;User ID=TestUser;Password=TestPassword;Initial Catalog=TestCatalogName;Provider=SQLNCLI11.1;Persist Security Info=True;Auto Translate=False;'
SELECT SUBSTRING(@TestConnectionString,CHARINDEX('ID=',@TestConnectionString)+3,CHARINDEX(';Password',@TestConnectionString)-CHARINDEX('ID=',@TestConnectionString)-3) 'User ID'
      ,SUBSTRING(@TestConnectionString,CHARINDEX(';Password=',@TestConnectionString)+10,CHARINDEX(';Initial',@TestConnectionString)-CHARINDEX(';Password=',@TestConnectionString)-10) 'Password'

如果字符串中存在不一致,这样的方法可能会失败,因此可能值得根据;分隔符将字符串拆分为字段。

如果您关心递归,SQL服务器可以处理它。我重写了rCTE查询(再次),我在另一个项目中使用它来提取值:

DECLARE @Test varchar(255) = 
'Data Source=123.45.67.890;User ID=TestUser;Password=TestPassword;Initial Catalog=TestCatalogName;Provider=SQLNCLI11.1;Persist Security Info=True;Auto Translate=False;'
;WITH T AS (
  SELECT
    StartIdx = CAST(0 as int),
    EndIdx = CAST(0 as int),
    Result = CAST('' as nvarchar(max))
  UNION ALL
  SELECT
    StartIdx = CAST(newstartidx AS int),
    EndIdx = CAST(EndIdx + newendidx as int),
    Result = CAST(newtoken as nvarchar(max))
  FROM 
    T
    CROSS APPLY(
      SELECT newstartidx = EndIdx + 1
    ) calc1
    CROSS APPLY(
      SELECT newtxt = substring(@Test, newstartidx, len(@Test))
    ) calc2
    CROSS APPLY(
      SELECT patidx = charindex(';', newtxt)
    ) calc3
    CROSS APPLY(
      SELECT newendidx = CASE 
        WHEN patidx = 0 THEN len(newtxt)
        ELSE patidx END
    ) calc4
    CROSS APPLY(
      SELECT newtoken = substring(@Test, newstartidx, newendidx)
    ) calc5
  WHERE newendidx > 0
) 
SELECT 
  --Result, 
  Name = left(Result, idx - 1),
  Value = substring(Result, idx + 1, len(Result) - idx - 1)
FROM 
  T
  CROSS APPLY (
    SELECT idx = charindex('=', Result)
  ) calc6
WHERE StartIdx != 0

使用一个通用的字符串分割函数两次(见下文)。调用一次以拆分名称-值对,再调用一次将名称与值分离。

在行动中看到它:http://sqlfiddle.com/#!3/3cce5/1/0

SELECT
  t3.[1] AS name,
  t3.[2] AS value
FROM dbo.strsplit(@TestConnectionString,';') t1
CROSS APPLY dbo.strsplit(t1.col,'=') t2
PIVOT(MAX(t2.col) FOR t2.n IN ([1],[2])) t3

我的字符串分割函数。

CREATE FUNCTION [dbo].[strsplit](
  @str varchar(max), --String to be split
  @dlm char(1)       --Delimiting character
)
RETURNS TABLE
RETURN (
WITH [cols] AS (
  SELECT
    1 AS [n],
    CAST(1 AS bigint) AS [idx],
    CHARINDEX(@dlm,@str,1) AS [ndx]
  UNION ALL
  SELECT
    [n] + 1,
    CHARINDEX(@dlm,@str,[idx]) + 1,
    CHARINDEX(@dlm,@str,[ndx] + 1)
  FROM [cols]
  WHERE CHARINDEX(@dlm,@str,[idx]) > 0
)
SELECT
  [n],
  CASE [ndx]
    WHEN 0 THEN SUBSTRING(@str,[idx],LEN(@str)-[idx]+1)
    ELSE SUBSTRING(@str,[idx],[ndx]-[idx])
  END AS [col]
FROM [cols])

最新更新