请教一句SQL

[ballatong]

字段A是个字符串字段，需要判断这个字符串字段中是否包含某个特定字符串，如果是，就取这个特定字符串后特定位数的字符
例如 ：
A 字段 如下内容：
abcdefghei dsfdf our number 123456 fdadsfaf

判断是否存在特定字符串‘our number’ 如果有 则取 ‘our number’ 字符串后的 6位字符 ‘123456’

结果需要取的是
123456

谢谢大神

[二月十六]

DECLARE @str NVARCHAR(MAX)='abcdefghei dsfdf our number 123456 fdadsfaf' --字符串
SELECT  CASE WHEN CHARINDEX('our number', @str) > 0
             THEN SUBSTRING(@str,
                            CHARINDEX('our number', @str) + LEN('our number')
                            + 1, 6)
             ELSE ''
        END

[ballatong]

好使，谢谢大神！

[文盲老顾]

;with t as (
	select 1 as id,'abcdefghei dsfdf our number 123456 fdadsfaf' as a
	union all
	select 2,'abcdefghei dsfdf our number 123456789 fdadsfaf' as a
	union all
	select 3,'abcdefghei dsfdf our number 12345-6789 fdadsfaf and our number 123' as a
),tt as (
	select a.*,b.id as loc,b.value from t a
	cross apply (select * from caigou_2017_query.dbo.splitstr(a.a,' ')) b
)
select * from tt a where exists(select top 1 1 from tt where loc=a.loc-2 and value='our') and exists(select top 1 1 from tt where loc=a.loc-1 and value='number')

[文盲老顾]

CREATE FUNCTION [dbo].[SplitStr]
(	
	@string nvarchar(max), 
	@symbol nvarchar(10)
)
RETURNS @table TABLE(id int identity,value nvarchar(max))
AS
begin 
	DECLARE @splitlen int
    SET @splitlen=LEN(@symbol)-1
    WHILE CHARINDEX(@symbol,@string)>0
    BEGIN
        INSERT @table(value) VALUES(LEFT(@string,CHARINDEX(@symbol,@string)-1))
        SET @string=STUFF(@string,1,CHARINDEX(@symbol,@string)+@splitlen,'')
    END
    INSERT @table(value) VALUES(@string)
    return
end
go

;with t as (
	select 1 as id,'abcdefghei dsfdf our number 123456 fdadsfaf' as a
	union all
	select 2,'abcdefghei dsfdf our number 123456789 fdadsfaf' as a
	union all
	select 3,'abcdefghei dsfdf our number 12345-6789 fdadsfaf and our number 123' as a
),tt as (
	select a.*,b.id as loc,b.value from t a
	cross apply (select * from dbo.splitstr(a.a,' ')) b
)
select * from tt a where exists(select top 1 1 from tt where loc=a.loc-2 and value='our') and exists(select top 1 1 from tt where loc=a.loc-1 and value='number')

忘了把函数扔出来