在T-SQL中实现和应用字符串拆分

I have this statement in T-SQL.

我在T-SQL中有这个声明。

SELECT Bay From TABLE where uid in (
    select B_Numbers from Info_Step WHERE uid = 'number'
)

I am selecting "multiple" BAYs from TABLE where their uid is equal to a string of numbers like this:

我从TABLE中选择“多个”BAY,其中uid等于一串数字,如下所示:

B_Numbers = 1:45:34:98

Therefore, I should be selecting 4 different BAYs from TABLE. I basically need to split the string 1:45:34:98 up into 4 different numbers.

因此,我应该从TABLE中选择4种不同的BAY。我基本上需要将字符串1:45:34:98分成4个不同的数字。

I'm thinking that Split() would work, but it doesn't and I get a syntax error. Any thoughts from the T-SQL gods would be awesome!

我认为Split()可以工作,但它没有,我得到语法错误。来自T-SQL神的任何想法都会很棒!

5 个解决方案

#1

Here is an implementation of a split function that returns the list of numbers as a table:

这是一个split函数的实现,它将数字列表作为表返回:

http://rbgupta.blogspot.com/2007/03/split-function-tsql.html

Looks like this would set you on your way...

看起来这会让你在路上......

#2

Here is a method that uses an auxiliary numbers table to parse the input string. The logic can easily be added to a function that returns a table. That table can then be joined to lookup the correct rows.

这是一个使用辅助数字表来解析输入字符串的方法。逻辑可以很容易地添加到返回表的函数中。然后可以连接该表以查找正确的行。

Step 1: Create the Numbers table

第1步:创建Numbers表

SET NOCOUNT ON
GO

IF EXISTS
(
    SELECT  1
    FROM    INFORMATION_SCHEMA.TABLES
    WHERE       TABLE_NAME  = 'Numbers'
            AND     TABLE_SCHEMA    = 'dbo'
            AND     TABLE_TYPE  = 'BASE TABLE'
)
BEGIN
    DROP TABLE dbo.Numbers
END
GO

CREATE TABLE dbo.Numbers
(
    Number smallint  IDENTITY(1, 1) PRIMARY KEY
)
GO

WHILE 1 = 1
BEGIN
    INSERT INTO dbo.Numbers DEFAULT VALUES

    IF SCOPE_IDENTITY() = 32767 
    BEGIN
        BREAK
    END
END
GO

Step 2: Parse the Input String

第2步:解析输入字符串

CREATE FUNCTION dbo.ParseString(@input_string varchar(8000), @delim varchar(8000) = " ")
RETURNS TABLE
AS RETURN
(
    SELECT Number 
    FROM dbo.Numbers
    WHERE CHARINDEX 
    ( 
        @delim + CONVERT(VARCHAR(12),Number) + @delim, 
        @delim + @input_string + @delim 
    ) > 0 
)
GO

**EXAMPLE**
SELECT * FROM dbo.ParseString('1:45:34:98',':')

Step 3: Use the results however you want/need

第3步:根据需要使用结果

Number
------
1
34
45
98

End-To-End Example

Create function that returns the appropriate BNumber (of course change it to use the commented out SQL)

创建返回相应BNumber的函数(当然更改它以使用注释掉的SQL)

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION dbo.GetBNumber (@uid int)
RETURNS VARCHAR(8000) 
AS
BEGIN
    RETURN '1:45:34:98'
    --select B_Numbers from Info_Step WHERE uid = @uid
END
GO

Use the use functions to return the desired results

使用use函数返回所需的结果

-- Using Test Data
SELECT N.Number FROM Numbers N
JOIN dbo.ParseString(dbo.GetBNumber(12345),':') Q ON Q.Number = N.Number

-- Using Your Data (Untested but should work.)
SELECT N.Bay
FROM TABLE N
JOIN dbo.ParseString(dbo.GetBNumber(ENTER YOU NUMBER HERE),':') Q ON Q.Number = N.uid

Results

Number
------
1
34
45
98

#3

You should keep your arrays as rows but if I understand your question I think this will work.

您应该将数组保留为行,但如果我理解您的问题,我认为这将有效。

SELECT 
    Bay 

From 
    TABLE
    join  Info_Step 
    on B_Numbers like '%'+ uid  +'%'

where 
    Info_Step.uid = 'number'

This query will do a full table scan because of the like operator.

由于like运算符,此查询将执行全表扫描。

#4

What you can do is loop through the B_Numbers entries and do your own split on : Insert those entries into a temp table and then perform your query.

你可以做的是循环遍历B_Numbers条目并自己进行拆分:将这些条目插入临时表,然后执行查询。

DECLARE @i             int
DECLARE @start         int
DECLARE @B_Numbers     nvarchar(20)
DECLARE @temp          table (
    number nvarchar(10)
)

-- SELECT B_Numbers FROM Info_Step WHERE uid = 'number'
SELECT @B_Numbers = '1:45:34:98'

SET @i = 0
SET @start = 0

-- Parse out characters delimited by ":";  
-- Would make a nice user defined function.
WHILE @i < len(@B_Numbers)
BEGIN
    IF substring(@B_Numbers, @i, 1) = ':'
    BEGIN
        INSERT INTO @temp
        VALUES (substring(@B_Numbers, @start, @i - @start))
        SET @start = @i + 1
    END

    SET @i = @i + 1
END

-- Insert last item
INSERT INTO @temp
VALUES (substring(@B_Numbers, @start, @i - @start + 1))

-- Do query with parsed values
SELECT Bay FROM TABLE WHERE uid in (SELECT * FROM @temp)

#5

You can even try this

你甚至可以尝试这个

declare @str varchar(50)
set @str = '1:45:34:98'

;with numcte as(
select 1 as rn union all select rn+1 from numcte where rn<LEN(@str)),
getchars as(select 
ROW_NUMBER() over(order by rn) slno,
rn,chars from numcte 
cross apply(select SUBSTRING(@str,rn,1) chars)X where chars = ':') 

select top 1 
    Bay1 = SUBSTRING(@str,0,(select rn from getchars where slno = 1)) 
    ,Bay2 = SUBSTRING(@str,
            (select rn from getchars where slno = 1) + 1,
            (((select rn from getchars where slno = 2)-
            (select rn from getchars where slno = 1)
            )-1))
    ,Bay3 = SUBSTRING(@str,
            (select rn from getchars where slno = 2) + 1,
            (((select rn from getchars where slno = 3)-
            (select rn from getchars where slno = 2)
            )-1))
    ,Bay4 = SUBSTRING(@str,
            (select rn from getchars where slno = 3)+1,
            LEN(@str))
    from getchars

Output:

Bay1    Bay2    Bay3    Bay4
1   45  34  98

#1