【场景】数据库知识,将字符串拆分成多行 【chatgpt】 【案例】 alterFUNCTIONdbo.SplitString ( @InputStringNVARCHAR(MAX), @DelimiterNVARCHAR(1) )RETURNS@OutputTableTABLE(idNVARCHAR(MAX))ASBEGINDECLARE@StartIndexINT, @EndIndexINTSET@StartIndex =1SET@EndIndex =CHARINDEX(@Delimiter, @InputString)WHILE@E...
CREATEORREPLACETYPE SplitStringWithSeqTableTypeASTABLEOFSplitStringWithSeqType; / CREATEORREPLACEFUNCTIONSplitStringWithSeq( p_stringINVARCHAR2, p_delimiterINVARCHAR2 )RETURNSplitStringWithSeqTableType PIPELINEDAS l_start_pos PLS_INTEGER := 1; l_end_pos PLS_INTEGER; l_seq_num PLS_INTEGER := 1;...
DELIMITER // CREATE FUNCTION SPLIT_STRING(str VARCHAR(255), delim VARCHAR(12), pos INT) RETURNS VARCHAR(255) BEGIN DECLARE output VARCHAR(255); SET output = REPLACE(SUBSTRING(SUBSTRING_INDEX(str, delim, pos), LENGTH(SUBSTRING_INDEX(str, delim, pos-1)) + 1), delim, ''); RETURN IF(ou...
Transact-SQL reference for the STRING_SPLIT function. This table-valued function splits a string into substrings based on a character delimiter.
CREATEFUNCTIONdbo.SplitString(@stringNVARCHAR(MAX),@delimiterCHAR(1))RETURNS@outputTABLE(Item NVARCHAR(MAX))ASBEGINDECLARE@startINT,@endINTSELECT@start=1,@end=CHARINDEX(@delimiter,@string)WHILE@start<LEN(@string)+1BEGINIF@end=0SET@end=LEN(@string)+1INSERTINTO@output(Item)VALUES(SUBSTRING(@strin...
SQL自定义函数split分隔字符串 一、F_Split:分割字符串拆分为数据表 Create FUNCTION [dbo].[F_Split] ( @SplitString nvarchar(max), --源字符串 @Separator nvarchar(10)=' ' --分隔符号,默认为空格 ) RETURNS @SplitStringsTable TABLE --输出的数据表...
首先GetSplitLength函数返回分割后的字符数组的长度。 1Createfunction[dbo].[GetSplitLength] 2( 3@Stringnvarchar(max),--要分割的字符串 4@Splitnvarchar(10)--分隔符号 5) 6returnsint 7as 8begin 9declare@locationint 10declare@startint 11declare@lengthint ...
Createfunction[dbo].[f_split](@aStringvarchar(max),@patternvarchar(10))returns @temptable(r int,avarchar(100))--实现split功能 的函数--select a from dbo.f_split('我:们a:a:b: ',':')asbegin declare @i int declare @row int
split(string,seprator) split函数传入两个参数,对string按照seprator进行拆分,这里就相当于把一个字符串变成了一个array explode 顾名思义,就是“爆炸”函数,就是把上面拆分的array给炸开,由一行三列,变为三行一列 lateral view 跟explode是一个固定搭配,相当于一种笛卡尔积,将爆炸函数得到的行跟class关联起来 ...
CREATE FUNCTION SPLIT_STR( x VARCHAR(255), delim VARCHAR(12), pos INT ) RETURNS VARCHAR(255) RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos), LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1), delim, ''); 定义完成后,通过命令:SELECT SPLIT_STR(string, delimiter, position) 使用...