SQL——自定义的fn_Split函数
SQL——自定义的fn_Split函数
--1. 创建fn_Split函数. ( 切分字符串, 返回一个列名为id的表 )
- if exists(select * from dbo.sysobjects where id = object_id('fn_Split') and (type = 'FN' or type = 'TF' or type='IF'))
- DROP FUNCTION fn_Split
- GO
- CREATE FUNCTION [dbo].[fn_Split]
- (
- @str VARCHAR(MAX),
- @separator VARCHAR(10)
- )
- RETURNS TABLE
- AS
- RETURN
- ( --Example: SELECT id FROM fn_Split('a,b,d,c',',')
- SELECT B.id
- FROM (
- SELECT [value] = CONVERT(XML, '<v>' + REPLACE(@str, @separator, '</v><v>') + '</v>')
- ) A
- OUTER APPLY(
- SELECT id = N.v.value('.', 'varchar(100)')
- FROM A.[value].nodes('/v') N(v)
- ) B
- )
- GO
评论暂时关闭