如何创建将一组字符串转换为整数的函数
在转向这里之前,我已经挣扎了几天了。
我正在尝试构建一个函数 StringToInteger,它将文本字符串转换为整数。我研究了网络,到目前为止,我已经有了一些代码,可以将变量中输入的一个字符串转换为整数。
DECLARE @YourString varchar(500)
SELECT @YourString='select'
;WITH AllNumbers AS
(
SELECT 1 AS Number
UNION ALL
SELECT Number+1
FROM AllNumbers
WHERE Number<LEN(@YourString)
)
SELECT
(SELECT
RIGHT('000'+CONVERT(varchar(max),ASCII(SUBSTRING(@YourString,Number,1))),3)
FROM AllNumbers
ORDER BY Number
FOR XML PATH(''), TYPE
).value('.','varchar(max)') AS NewValue
--OPTION (MAXRECURSION 500) --<<needed if you have a string longer than 100
我还找到了一些关于如何查询表 TableB 并将其结果发送到函数并运行整个集合的代码。
SELECT
*
FROM
TableB
CROSS APPLY
dbo.StringToInteger(TableB.ColumnWithText) AS IntegerOutOfText
我的问题是,应该如何调整顶部的代码,以便在运行时将其作为函数加载到数据库中?我做了多少尝试,我都无法让 SQL Server 将其作为函数导入。
我的另一个问题是,应该如何调整底部代码,以便它调用该函数并将整个查询的结果集转换为一组数字。
此致 丹尼尔
I have been struggling for a couple of days now before turning here.
I'm trying to build a function, StringToInteger, that converts a textstring to integer. I have researched the web and gotten so far that I have some code that convert one string, entered in a variable, to an integer.
DECLARE @YourString varchar(500)
SELECT @YourString='select'
;WITH AllNumbers AS
(
SELECT 1 AS Number
UNION ALL
SELECT Number+1
FROM AllNumbers
WHERE Number<LEN(@YourString)
)
SELECT
(SELECT
RIGHT('000'+CONVERT(varchar(max),ASCII(SUBSTRING(@YourString,Number,1))),3)
FROM AllNumbers
ORDER BY Number
FOR XML PATH(''), TYPE
).value('.','varchar(max)') AS NewValue
--OPTION (MAXRECURSION 500) --<<needed if you have a string longer than 100
I also found some code on how to query a table, TableB, and send its result to a function and have the whole set run.
SELECT
*
FROM
TableB
CROSS APPLY
dbo.StringToInteger(TableB.ColumnWithText) AS IntegerOutOfText
My question is, how should the code at the top be adjusted so that it is loaded into the db as a function when run? How much I try, I cant get SQL Server import it as a function.
And my other question is, how should the bottom code be adjusted so that it calls the function and have it convert the whole query's result set to a set numbers number.
Best regards
Daniel
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
该函数可能如下所示:
使用如下:
与如下表一起使用:
The function could look like this:
Use like this:
Use with a table like this:
在进行转换之前,您真的需要解析一些子字符串吗?
TO_NUMBER()
怎么样?这看起来简单多了。Do you really have some substring to parse out before you do the conversion?
what about
TO_NUMBER()
? that seems alot simpler.