我想在sqll中创建split函数如何创建它 [英] I want to create split function in sqll how to create it

问题描述

我想在sqll中创建分割函数。但代码显示以下错误



消息8116，级别16，状态1，过程fnSplit，第13行

参数数据类型varchar对子字符串函数的参数2无效。



我尝试过：



我的代码在这里



创建功能fnSplit

（

@x VARCHAR（255），

@ delim VARCHAR（12），

@pos INT

）

返回NVARCHAR（500）



BEGIN

DECLARE @Rtntxt NVARCHAR（500）;

SET @ Rtntxt ='';

SET @ Rtntxt = REPLACE（SUBSTRING（@x，@ delim，@ post），（LEN（SUBSTRING（@x，@ delim，@ sub - 1）+ 1）），@ delim） ;

RETURN @Rtntxt;

END

I want to Create Split Function in sqll .But the code shows an The Following Error

Msg 8116, Level 16, State 1, Procedure fnSplit, Line 13
Argument data type varchar is invalid for argument 2 of substring function.

What I have tried:

Mycode is here

CREATE FUNCTION fnSplit
(
@x VARCHAR(255),
@delim VARCHAR(12),
@pos INT
)
RETURNS NVARCHAR(500)

BEGIN
DECLARE @Rtntxt NVARCHAR(500);
SET @Rtntxt='';
SET @Rtntxt=REPLACE(SUBSTRING(@x, @delim, @pos), (LEN(SUBSTRING(@x, @delim, @pos - 1)+ 1)), @delim);
RETURN @Rtntxt;
END

推荐答案

基本上，这是一个坏主意 - 它反对First Normal表单在您的数据库中包含非原子元素，这是该函数应该提供的帮助。而且他们总是比他们节省时间更加悲伤 - 所以重新设计你的数据库以删除它们总是比花时间打破它们更好，更好的解决方案。



话虽如此，错误信息非常明确：

Basically, it's a bad idea - it's against First Normal Form to have non-atomic elements in your DB, which is what that function is supposed to help with. And they always give a lot more grief than they save time - so redesigning your DB to remove them is always a much, much better solution than spending time breaking them up.

Having said that, the error message is very clear:

Argument data type varchar is invalid for argument 2 of substring function.

快速浏览SUBSTRING文档： SQL Server SUBSTRING（）函数 [ ^ ]会向您显示参数2必须是数字，而不是分隔符字符串。



SQL最多 - 糟糕的字符串处理，这是将非原子数据存储在DB中的字符串中的原因之一是一个非常糟糕的主意。但有可能：在SQL IN中使用逗号分隔值参数字符串条款 [ ^ ]或转换逗号将列中的数据分隔为行以供选择 [ ^ ]可以帮助您分解字符串。

A quick look at the SUBSTRING documentation: SQL Server SUBSTRING() Function[^] would have shown you that parameter 2 must be a number, not a delimiter string.

SQL has - at best - poor string handling, which is one of the reasons that storing non-atomic data in strings in DB's is a very bad idea. But it is possible: Using comma separated value parameter strings in SQL IN clauses[^] or Converting comma separated data in a column to rows for selection[^] may help you to break up the string.

这篇关于我想在sqll中创建split函数如何创建它的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！