找到子字符串/ charindex t - sql(复制)

This question already has an answer here:

这个问题已经有了答案:

• How do I split a string so I can access item x? 41 answers
• 如何分割字符串以便访问项目x?41岁的答案

I have the following variable.

我有下面的变量。

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;'

I want to separate out each property's values from this connection string.

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

I am sure that I have to use SUBSTRING and CHARINDEX but not sure how. I don't want to hard-code the length for each property as user_id could be "Comeonedude"

我确定我必须使用子字符串和CHARINDEX，但不确定如何使用。我不想硬编码每个属性的长度user_id可以是" comonedude "

Can someone show me how I can extract few of these properties as an example?

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

In meanwhile, I will try to see if I can figure out anything.

与此同时，我将试着看看我是否能弄明白什么。

Thank you

谢谢你！

5 个解决方案

#1

I like using XML casting to split strings in TSQL. This method is preferred because it doesn't require you to create string split functions all over the place and in my experience it performs and scales well. Here is a SQLFiddle example.

我喜欢在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)

Explanation:

解释:

The @TestConnectionString is formatted as an XML document by this select statement:

@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

The XML string begins with <root><pair><property>, then the REPLACE function replaces each of the delimiting semicolons with </value></pair><pair><property> and replaces each of the separating equal signs with </property><value>. The @TestConnectionString ends with a semicolon, so that semicolon must first be removed by the LEFT function, or else we would end up with an extra </value></pair><pair><property> at the end of our XML string. The XML string is completed by appending </value></pair></root>, and we end up with this:

XML字符串以 .然后替换函数用 用 替换每个分隔等号。@TestConnectionString以一个分号结尾，因此必须首先用左函数删除分号，否则我们将在XML字符串末尾得到一个额外的 。XML字符串通过添加 完成，我们得到:

<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>

The XML string is converted to the XML data type with the CAST function. The CROSS APPLY operator can be used to turn the nodes of a XML document into a table-like object (aliased as t) with rows and columns (aliased as c).

使用CAST函数将XML字符串转换为XML数据类型。CROSS APPLY操作符可用于将XML文档的节点转换为类似表的对象(别名t)，其中包含行和列(别名c)。

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

Now we have a table with rows representing each pair node in the XML document. This table can be selected from, using the value function to assign a data type to each column that we want to select out.

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

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

#2

First split the string at ';' .. You can find many Split functions online. Use one that splits it into a table.

首先将字符串拆分为';'。你可以在网上找到许多拆分功能。使用一个将它分割成一个表的方法。

Following Code is from: How to split string using delimiter char using T-SQL?

下面的代码来自:如何使用T-SQL使用分隔符char分割字符串?

 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

assuming the order is always the same, split each of the resutls at the '='. take the right part of every string (the length of the remaining string after '=')..

假设顺序总是相同的，将每个resutls分割为'='。取每个字符串的右部分('='后剩余字符串的长度)。

et voilà, you have every property with its value.

瞧，每个属性都有它的值。

-- EDIT: With the Split Function from above:

——编辑:上面分割功能:

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

The result will be in the #ressult table:

结果将在#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           ║
╚═══════════════════════╩═════════════════╝

EDIT: Or you can create a stored procedure:

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

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

have fun, You're wellcome = )

祝你玩得愉快!

#3

Here is the general approach you could use if you really wanted to use SUBSTRING without hard-coded numbers:

如果你真的想使用没有硬编码数字的子字符串，你可以使用以下的一般方法:

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'

An approach like this can fail if there are inconsistencies in your strings, it may be worth splitting the string to fields based on the ; delimiter.

这样的方法可能会失败，如果您的字符串中有不一致的地方，它可能值得根据;分隔符。

#4

If you care about recursion, SQL server can handle that. I rewrote rCTE query (once more) I use in another project to extract the values:

如果您关心递归，SQL server可以处理它。我重写了在另一个项目中使用的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

#5

Use a generic string-splitting function twice (see below). Call it once to split the name-value pairs and again to separate the names from the values.

使用一个通用的字符串分割函数两次(见下面)。调用它一次，以拆分名称-值对，并再次将名称与值分隔开。

See it in action: http://sqlfiddle.com/#!3/3cce5/1/0

请参见下面的操作:http://sqlfiddle.com/#!

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

My string-split function.

我string-split函数。

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])