如何创建将一组字符串转换为整数的函数

发布于 2024-11-25 17:31:44 字数 985 浏览 0 评论 0原文

在转向这里之前,我已经挣扎了几天了。

我正在尝试构建一个函数 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 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(2

花海 2024-12-02 17:31:44

该函数可能如下所示:

create function StringToInteger(@YourString varchar(500))
returns varchar(1500) as
begin

  declare @Ret varchar(1500);

  WITH AllNumbers AS
  (
  SELECT 1 AS Number
          UNION ALL
          SELECT Number+1
      FROM AllNumbers
      WHERE Number<LEN(@YourString)
  )
  SELECT @Ret =
     (SELECT
          RIGHT('000'+CONVERT(varchar(max),ASCII(SUBSTRING(@YourString,Number,1))),3)
          FROM AllNumbers
          ORDER BY Number
          FOR XML PATH(''), TYPE
     ).value('.','varchar(max)');

  return @Ret;
end

使用如下:

DECLARE @YourString   varchar(500)
SELECT @YourString='select'

select dbo.StringToInteger(@YourString)

与如下表一起使用:

declare @T table (Col1 varchar(10))
insert into @T values('select')

select dbo.StringToInteger(Col1)
from @T

The function could look like this:

create function StringToInteger(@YourString varchar(500))
returns varchar(1500) as
begin

  declare @Ret varchar(1500);

  WITH AllNumbers AS
  (
  SELECT 1 AS Number
          UNION ALL
          SELECT Number+1
      FROM AllNumbers
      WHERE Number<LEN(@YourString)
  )
  SELECT @Ret =
     (SELECT
          RIGHT('000'+CONVERT(varchar(max),ASCII(SUBSTRING(@YourString,Number,1))),3)
          FROM AllNumbers
          ORDER BY Number
          FOR XML PATH(''), TYPE
     ).value('.','varchar(max)');

  return @Ret;
end

Use like this:

DECLARE @YourString   varchar(500)
SELECT @YourString='select'

select dbo.StringToInteger(@YourString)

Use with a table like this:

declare @T table (Col1 varchar(10))
insert into @T values('select')

select dbo.StringToInteger(Col1)
from @T
相守太难 2024-12-02 17:31:44

在进行转换之前,您真的需要解析一些子字符串吗?

TO_NUMBER() 怎么样?这看起来简单多了。

Do you really have some substring to parse out before you do the conversion?

what about TO_NUMBER()? that seems alot simpler.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文