集計 - 正規 表現 分割 sql




アイテムxにアクセスできるように文字列を分割するにはどうすればよいですか? (20)

これは、文字列を分割して項目Xにアクセスするという質問の目的を達成する関数です。

CREATE FUNCTION [dbo].[SplitString]
(
   @List       VARCHAR(MAX),
   @Delimiter  VARCHAR(255),
   @ElementNumber INT
)
RETURNS VARCHAR(MAX)
AS
BEGIN

       DECLARE @inp VARCHAR(MAX)
       SET @inp = (SELECT REPLACE(@List,@Delimiter,'_DELMTR_') FOR XML PATH(''))

       DECLARE @xml XML
       SET @xml = '<split><el>' + REPLACE(@inp,'_DELMTR_','</el><el>') + '</el></split>'

       DECLARE @ret VARCHAR(MAX)
       SET @ret = (SELECT
              el = split.el.value('.','varchar(max)')
       FROM  @xml.nodes('/split/el[string-length(.)>0][position() = sql:variable("@elementnumber")]') split(el))

       RETURN @ret

END

使用法:

SELECT dbo.SplitString('Hello John Smith', ' ', 2)

結果:

John

SQL Serverを使用して、項目xにアクセスできるように文字列を分割する方法を教えてください。

文字列 "Hello John Smith"を取ります。 文字列をスペースで区切って "John"を返すインデックス1のアイテムにアクセスするにはどうすればよいですか?


これは、文字列内の特定のトークンを取得するために私がしたことです。 (MSSQL 2008でテスト済み)

(で見つかった:まず、以下の機能を作成し 、ここで

CREATE FUNCTION dbo.SplitStrings_Moden
(
   @List NVARCHAR(MAX),
   @Delimiter NVARCHAR(255)
)
RETURNS TABLE
WITH SCHEMABINDING AS
RETURN
  WITH E1(N)        AS ( SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 
                         UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 
                         UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1),
       E2(N)        AS (SELECT 1 FROM E1 a, E1 b),
       E4(N)        AS (SELECT 1 FROM E2 a, E2 b),
       E42(N)       AS (SELECT 1 FROM E4 a, E2 b),
       cteTally(N)  AS (SELECT 0 UNION ALL SELECT TOP (DATALENGTH(ISNULL(@List,1))) 
                         ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E42),
       cteStart(N1) AS (SELECT t.N+1 FROM cteTally t
                         WHERE (SUBSTRING(@List,t.N,1) = @Delimiter OR t.N = 0))
  SELECT Item = SUBSTRING(@List, s.N1, ISNULL(NULLIF(CHARINDEX(@Delimiter,@List,s.N1),0)-s.N1,8000))
    FROM cteStart s;

そして

create FUNCTION dbo.getToken
(
@List NVARCHAR(MAX),
@Delimiter NVARCHAR(255),
@Pos int
)
RETURNS varchar(max)
as 
begin
declare @returnValue varchar(max);
select @returnValue = tbl.Item from (
select ROW_NUMBER() over (order by (select null)) as id, * from dbo.SplitStrings_Moden(@List, @Delimiter)
) as tbl
where tbl.id = @Pos
return @returnValue
end

それならあなたはそのようにそれを使うことができます:

select dbo.getToken('1111_2222_3333_', '_', 1)

1111を返す


アーロンベルトランの答えは素晴らしいですが、欠陥があります。 length関数は末尾のスペースを削除するため、スペースを区切り文字として正確に処理しません(元の質問の例のように)。

以下は彼のコードで、スペース区切り文字を考慮して少し調整しています。

CREATE FUNCTION [dbo].[SplitString]
(
    @List NVARCHAR(MAX),
    @Delim VARCHAR(255)
)
RETURNS TABLE
AS
    RETURN ( SELECT [Value] FROM 
      ( 
        SELECT 
          [Value] = LTRIM(RTRIM(SUBSTRING(@List, [Number],
          CHARINDEX(@Delim, @List + @Delim, [Number]) - [Number])))
        FROM (SELECT Number = ROW_NUMBER() OVER (ORDER BY name)
          FROM sys.all_objects) AS x
          WHERE Number <= LEN(@List)
          AND SUBSTRING(@Delim + @List, [Number], LEN(@Delim+'x')-1) = @Delim
      ) AS y
    );

以降では 、SQL Serverの2016年 、私たちは string_split

DECLARE @string varchar(100) = 'Richard, Mike, Mark'

SELECT value FROM string_split(@string, ',')

関数を必要とせずにSQLで文字列を分割することができます。

DECLARE @bla varchar(MAX)
SET @bla = 'BED40DFC-F468-46DD-8017-00EF2FA3E4A4,64B59FC5-3F4D-4B0E-9A48-01F3D4F220B0,A611A108-97CA-42F3-A2E1-057165339719,E72D95EA-578F-45FC-88E5-075F66FD726C'

-- http://.com/questions/14712864/how-to-query-values-from-xml-nodes
SELECT 
    x.XmlCol.value('.', 'varchar(36)') AS val 
FROM 
(
    SELECT 
    CAST('<e>' + REPLACE(@bla, ',', '</e><e>') + '</e>' AS xml) AS RawXml
) AS b 
CROSS APPLY b.RawXml.nodes('e') x(XmlCol);

任意の文字列(xml特殊文字を含む)をサポートする必要がある場合

DECLARE @bla NVARCHAR(MAX)
SET @bla = '<html>unsafe & safe Utf8CharsDon''tGetEncoded ÄöÜ - "Conex"<html>,Barnes & Noble,abc,def,ghi'

-- http://.com/questions/14712864/how-to-query-values-from-xml-nodes
SELECT 
    x.XmlCol.value('.', 'nvarchar(MAX)') AS val 
FROM 
(
    SELECT 
    CAST('<e>' + REPLACE((SELECT @bla FOR XML PATH('')), ',', '</e><e>') + '</e>' AS xml) AS RawXml
) AS b 
CROSS APPLY b.RawXml.nodes('e') x(XmlCol); 

SQL Serverには分割関数が組み込まれているとは思わないので、UDF以外にPARSENAME関数を乗っ取ることが唯一の解決策です。

SELECT PARSENAME(REPLACE('Hello John Smith', ' ', '.'), 2) 

PARSENAMEは文字列を取り、それをピリオド文字に分割します。 それは2番目の引数として数を取り、その数は文字列のどのセグメントを返すかを指定します(後ろから前へと働きます)。

SELECT PARSENAME(REPLACE('Hello John Smith', ' ', '.'), 3)  --return Hello

明らかな問題は、文字列がすでにピリオドを含んでいるときです。 私はまだUDFを使用するのが最善の方法だと思います...他の提案はありますか?


ここで私は簡単な解決方法を投稿します

CREATE FUNCTION [dbo].[split](
          @delimited NVARCHAR(MAX),
          @delimiter NVARCHAR(100)
        ) RETURNS @t TABLE (id INT IDENTITY(1,1), val NVARCHAR(MAX))
        AS
        BEGIN
          DECLARE @xml XML
          SET @xml = N'<t>' + REPLACE(@delimited,@delimiter,'</t><t>') + '</t>'

          INSERT INTO @t(val)
          SELECT  r.value('.','varchar(MAX)') as item
          FROM  @xml.nodes('/t') as records(r)
          RETURN
        END


このような機能を実行する

  select * from dbo.split('Hello John Smith',' ')

このパターンはうまくいき、一般化することができます

Convert(xml,'<n>'+Replace(FIELD,'.','</n><n>')+'</n>').value('(/n[INDEX])','TYPE')
                          ^^^^^                                   ^^^^^     ^^^^

FIELD INDEX 、および TYPEに 注意してください。

のような識別子を持ついくつかのテーブルを見ましょう

sys.message.1234.warning.A45
sys.message.1235.error.O98
....

その後、あなたは書くことができます

SELECT Source         = q.value('(/n[1])', 'varchar(10)'),
       RecordType     = q.value('(/n[2])', 'varchar(20)'),
       RecordNumber   = q.value('(/n[3])', 'int'),
       Status         = q.value('(/n[4])', 'varchar(5)')
FROM   (
         SELECT   q = Convert(xml,'<n>'+Replace(fieldName,'.','</n><n>')+'</n>')
         FROM     some_TABLE
       ) Q

すべての部品を分割して鋳造する。


これを行うUDFがあります。 すべてのシナリオを試したわけではありませんが、区切られた値の表が返されますが、あなたの例はうまくいきます。


CREATE FUNCTION SplitString 
(
    -- Add the parameters for the function here
    @myString varchar(500),
    @deliminator varchar(10)
)
RETURNS 
@ReturnTable TABLE 
(
    -- Add the column definitions for the TABLE variable here
    [id] [int] IDENTITY(1,1) NOT NULL,
    [part] [varchar](50) NULL
)
AS
BEGIN
        Declare @iSpaces int
        Declare @part varchar(50)

        --initialize spaces
        Select @iSpaces = charindex(@deliminator,@myString,0)
        While @iSpaces > 0

        Begin
            Select @part = substring(@myString,0,charindex(@deliminator,@myString,0))

            Insert Into @ReturnTable(part)
            Select @part

    Select @myString = substring(@mystring,charindex(@deliminator,@myString,0)+ len(@deliminator),len(@myString) - charindex(' ',@myString,0))


            Select @iSpaces = charindex(@deliminator,@myString,0)
        end

        If len(@myString) > 0
            Insert Into @ReturnTable
            Select @myString

    RETURN 
END
GO

これを次のように呼ぶでしょう。


Select * From SplitString('Hello John Smith',' ')

編集:以下のようにlen> 1の区切り文字を処理するための解決法を更新しました。


select * From SplitString('Hello**John**Smith','**')

これを試して:

CREATE function [SplitWordList]
(
 @list varchar(8000)
)
returns @t table 
(
 Word varchar(50) not null,
 Position int identity(1,1) not null
)
as begin
  declare 
    @pos int,
    @lpos int,
    @item varchar(100),
    @ignore varchar(100),
    @dl int,
    @a1 int,
    @a2 int,
    @z1 int,
    @z2 int,
    @n1 int,
    @n2 int,
    @c varchar(1),
    @a smallint
  select 
    @a1 = ascii('a'),
    @a2 = ascii('A'),
    @z1 = ascii('z'),
    @z2 = ascii('Z'),
    @n1 = ascii('0'),
    @n2 = ascii('9')
  set @ignore = '''"'
  set @pos = 1
  set @dl = datalength(@list)
  set @lpos = 1
  set @item = ''
  while (@pos <= @dl) begin
    set @c = substring(@list, @pos, 1)
    if (@ignore not like '%' + @c + '%') begin
      set @a = ascii(@c)
      if ((@a >= @a1) and (@a <= @z1))  
        or ((@a >= @a2) and (@a <= @z2))
        or ((@a >= @n1) and (@a <= @n2))
      begin
        set @item = @item + @c
      end else if (@item > '') begin
        insert into @t values (@item)
        set @item = ''
      end
    end 
    set @pos = @pos + 1
  end
  if (@item > '') begin
    insert into @t values (@item)
  end
  return
end

このようにテストしてください。

select * from SplitWordList('Hello John Smith')

まず、関数を作成します(CTEを使用すると、一般的なテーブル式では一時テーブルが不要になります)。

 create function dbo.SplitString 
    (
        @str nvarchar(4000), 
        @separator char(1)
    )
    returns table
    AS
    return (
        with tokens(p, a, b) AS (
            select 
                1, 
                1, 
                charindex(@separator, @str)
            union all
            select
                p + 1, 
                b + 1, 
                charindex(@separator, @str, b + 1)
            from tokens
            where b > 0
        )
        select
            p-1 zeroBasedOccurance,
            substring(
                @str, 
                a, 
                case when b > 0 then b-a ELSE 4000 end) 
            AS s
        from tokens
      )
    GO

次に、これを任意のテーブルとして使用します(または既存のストアドプロシージャ内に収まるように変更します)。

select s 
from dbo.SplitString('Hello John Smith', ' ')
where zeroBasedOccurance=1

更新

以前のバージョンは4000文字より長い入力文字列では失敗するでしょう。 このバージョンでは、制限があります。

create function dbo.SplitString 
(
    @str nvarchar(max), 
    @separator char(1)
)
returns table
AS
return (
with tokens(p, a, b) AS (
    select 
        cast(1 as bigint), 
        cast(1 as bigint), 
        charindex(@separator, @str)
    union all
    select
        p + 1, 
        b + 1, 
        charindex(@separator, @str, b + 1)
    from tokens
    where b > 0
)
select
    p-1 ItemIndex,
    substring(
        @str, 
        a, 
        case when b > 0 then b-a ELSE LEN(@str) end) 
    AS s
from tokens
);

GO

使い方は変わりません。


データベースの互換性レベルが130以上の場合は、 OFFSET FETCH 句とともに STRING_SPLIT 関数を使用して、インデックスで特定の項目を取得できます。

インデックスN (ゼロベース)のアイテムを取得するには、次のコードを使用できます。

SELECT value
FROM STRING_SPLIT('Hello John Smith',' ')
ORDER BY (SELECT NULL)
OFFSET N ROWS
FETCH NEXT 1 ROWS ONLY

データベースの互換性レベル を確認するには、次のコードを実行します。

SELECT compatibility_level  
FROM sys.databases WHERE name = 'YourDBName';

数値テーブルを利用して文字列解析を実行できます。

物理番号テーブルを作成します。

    create table dbo.Numbers (N int primary key);
    insert into dbo.Numbers
        select top 1000 row_number() over(order by number) from master..spt_values
    go

1000000行のテストテーブルを作成します

    create table #yak (i int identity(1,1) primary key, array varchar(50))

    insert into #yak(array)
        select 'a,b,c' from dbo.Numbers n cross join dbo.Numbers nn
    go

関数を作成する

    create function [dbo].[ufn_ParseArray]
        (   @Input      nvarchar(4000), 
            @Delimiter  char(1) = ',',
            @BaseIdent  int
        )
    returns table as
    return  
        (   select  row_number() over (order by n asc) + (@BaseIdent - 1) [i],
                    substring(@Input, n, charindex(@Delimiter, @Input + @Delimiter, n) - n) s
            from    dbo.Numbers
            where   n <= convert(int, len(@Input)) and
                    substring(@Delimiter + @Input, n, 1) = @Delimiter
        )
    go

使用方法(私のラップトップで40秒で3mil行を出力します)

    select * 
    from #yak 
    cross apply dbo.ufn_ParseArray(array, ',', 1)

掃除

    drop table dbo.Numbers;
    drop function  [dbo].[ufn_ParseArray]

ここでのパフォーマンスは驚くべきことではありませんが、100万行のテーブルに対して関数を呼び出すことは最善の方法ではありません。 多くの行に分割された文字列を実行する場合は、この機能を避けます。


次の例では再帰的CTEを使用しています

更新 18.09.2013

CREATE FUNCTION dbo.SplitStrings_CTE(@List nvarchar(max), @Delimiter nvarchar(1))
RETURNS @returns TABLE (val nvarchar(max), [level] int, PRIMARY KEY CLUSTERED([level]))
AS
BEGIN
;WITH cte AS
 (
  SELECT SUBSTRING(@List, 0, CHARINDEX(@Delimiter,  @List + @Delimiter)) AS val,
         CAST(STUFF(@List + @Delimiter, 1, CHARINDEX(@Delimiter, @List + @Delimiter), '') AS nvarchar(max)) AS stval, 
         1 AS [level]
  UNION ALL
  SELECT SUBSTRING(stval, 0, CHARINDEX(@Delimiter, stval)),
         CAST(STUFF(stval, 1, CHARINDEX(@Delimiter, stval), '') AS nvarchar(max)),
         [level] + 1
  FROM cte
  WHERE stval != ''
  )
  INSERT @returns
  SELECT REPLACE(val, ' ','' ) AS val, [level]
  FROM cte
  WHERE val > ''
  RETURN
END

SQLFiddle デモ


私はfredericの答えを使用しますが、これはSQL Server 2005では機能しませんでした

私はそれを修正しましたそして私は union allunion all select を使ってい select そしてそれは働きます

DECLARE @str varchar(max)
SET @str = 'Hello John Smith how are you'

DECLARE @separator varchar(max)
SET @separator = ' '

DECLARE @Splited table(id int IDENTITY(1,1), item varchar(max))

SET @str = REPLACE(@str, @separator, ''' UNION ALL SELECT ''')
SET @str = ' SELECT  ''' + @str + '''  ' 

INSERT INTO @Splited
EXEC(@str)

SELECT * FROM @Splited

そして結果セットは:

id  item
1   Hello
2   John
3   Smith
4   how
5   are
6   you

私はそれが古い質問であることを知っています、しかし私は誰かが私の解決策から利益を得ることができると思います。

select 
SUBSTRING(column_name,1,CHARINDEX(' ',column_name,1)-1)
,SUBSTRING(SUBSTRING(column_name,CHARINDEX(' ',column_name,1)+1,LEN(column_name))
    ,1
    ,CHARINDEX(' ',SUBSTRING(column_name,CHARINDEX(' ',column_name,1)+1,LEN(column_name)),1)-1)
,SUBSTRING(SUBSTRING(column_name,CHARINDEX(' ',column_name,1)+1,LEN(column_name))
    ,CHARINDEX(' ',SUBSTRING(column_name,CHARINDEX(' ',column_name,1)+1,LEN(column_name)),1)+1
    ,LEN(column_name))
from table_name

SQL FIDDLE

利点:

  • これは、3つのサブストリング区切り文字すべてを「」で区切ります。
  • パフォーマンスが低下するため、whileループを使用しないでください。
  • 結果のすべてのサブストリングが1行に表示されるため、ピボットする必要はありません。

制限事項

  • 合計を知っている必要があります。 スペース(サブストリング)

:この解はNまでの部分文字列を与えることができます。

制限を克服するために、我々は以下の ref 使用することができます。

しかし、やはり上記の ref はテーブルでは使用できません(実際には使用できませんでした)。

この解決策が誰かに役立つことを願っています。

更新: Records> 50000の場合、 パフォーマンス を低下させるので LOOPS を使用することは お勧めでき ません。


STRING_SPLIT を使用した最新のアプローチ では、SQL Server 2016以降が必要です。

DECLARE @string varchar(100) = 'Hello John Smith'

SELECT
    ROW_NUMBER() OVER (ORDER BY value) AS RowNr,
    value
FROM string_split(@string, ' ')

結果:

RowNr   value
1       Hello
2       John
3       Smith

これで、行番号からn番目の要素を取得することができます。


TVF with recursive を使った純粋な集合ベースの解法 CTE 。 あなたはできる JOIN そして APPLY この関数はどんなデータセットにも。

create function [dbo].[SplitStringToResultSet] (@value varchar(max), @separator char(1))
returns table
as return
with r as (
    select value, cast(null as varchar(max)) [x], -1 [no] from (select rtrim(cast(@value as varchar(max))) [value]) as j
    union all
    select right(value, len(value)-case charindex(@separator, value) when 0 then len(value) else charindex(@separator, value) end) [value]
    , left(r.[value], case charindex(@separator, r.value) when 0 then len(r.value) else abs(charindex(@separator, r.[value])-1) end ) [x]
    , [no] + 1 [no]
    from r where value > '')

select ltrim(x) [value], [no] [index] from r where x is not null;
go

使用法:

select *
from [dbo].[SplitStringToResultSet]('Hello John Smith', ' ')
where [index] = 1;

結果:

value   index
-------------
John    1

SQL コードで 区切られた文字列を解析する関数が 役に立つことがあります( コードプロジェクトより )。

あなたはこの簡単なロジックを使うことができます:

Declare @products varchar(200) = '1|20|3|343|44|6|8765'
Declare @individual varchar(20) = null

WHILE LEN(@products) > 0
BEGIN
    IF PATINDEX('%|%', @products) > 0
    BEGIN
        SET @individual = SUBSTRING(@products,
                                    0,
                                    PATINDEX('%|%', @products))
        SELECT @individual

        SET @products = SUBSTRING(@products,
                                  LEN(@individual + '|') + 1,
                                  LEN(@products))
    END
    ELSE
    BEGIN
        SET @individual = @products
        SET @products = NULL
        SELECT @individual
    END
END

最初と最後の名前を解析するための簡単なソリューション

DECLARE @Name varchar(10) = 'John Smith'

-- Get First Name
SELECT SUBSTRING(@Name, 0, (SELECT CHARINDEX(' ', @Name)))

-- Get Last Name
SELECT SUBSTRING(@Name, (SELECT CHARINDEX(' ', @Name)) + 1, LEN(@Name))

私の場合(そして他の多くの人ではそうです...)、私は単一のスペースで区切られた姓と名のリストを持っています。 これはselectステートメントの中で直接使用して、名と姓を解析することができます。

-- i.e. Get First and Last Name from a table of Full Names
SELECT SUBSTRING(FullName, 0, (SELECT CHARINDEX(' ', FullName))) as FirstName,
SUBSTRING(FullName, (SELECT CHARINDEX(' ', FullName)) + 1, LEN(FullName)) as LastName,
From FullNameTable




split