java 把数字替换掉_连续数字使用连接符替换

「爱情、让人受尽委屈。」 2022-11-11 14:48 167阅读 0赞

比如下面一串字符，把连续的数字使用“-”连接起来。

DECLARE @Source NVARCHAR(MAX) = N'1,2,3,5,6,7,9,10,33,34,35,36,37,100,101,102,103,104,111,142,137,188,189,200,205,206'

结果要求如下：

![922f7d35e58272b01e20e21a989b43f5.png][]

解决问题，Insus.NET创建2个自定义函数：

![0ddc4147c8f04382ca881c6eedd15634.png][]

![d163a4346f648c7d7fcfd8c086befbc7.gif][]

![a5a6c5537303c06270c4fe3a4338aa60.gif][]

SET ANSI\_NULLS ON

SET QUOTED\_IDENTIFIER ON

\-- =============================================

\-- Author: Insus.NET

\-- Create date: 2019-05-27

\-- Update date: 2019-05-27

\-- Description: 分割字符串

\-- =============================================

CREATE FUNCTION \[dbo\].\[tvf\_SplitStringAsTwoField\]

(

@Source NVARCHAR(MAX)

)

RETURNS @returnResult TABLE

(

\[From\] INT NOT NULL,

\[To\] INT NOT NULL

)

BEGIN

SET @Source = @Source + N',';

IF CHARINDEX(',',@Source) > 0

BEGIN

WHILE CHARINDEX(',', @Source) > 0

BEGIN

DECLARE @CutoutString NVARCHAR(MAX) = SUBSTRING(@Source, 0, CHARINDEX(',', @Source))

SET @Source = LTRIM(RTRIM(SUBSTRING(@Source, CHARINDEX(',', @Source) + 1, LEN(@Source))))

DECLARE @from INT,@to INT

SELECT TOP 1 @from = \[From\], @to = (\[To\]) FROM @returnResult ORDER BY \[From\] DESC

IF @from IS NULL AND @to IS NULL

INSERT INTO @returnResult (\[From\],\[To\]) VALUES(@CutoutString,@CutoutString)

ELSE

BEGIN

IF @to + 1 = CAST(@CutoutString AS INT)

UPDATE @returnResult SET \[To\] = @CutoutString WHERE \[From\] = @from

ELSE

INSERT INTO @returnResult (\[From\],\[To\]) VALUES(@CutoutString,@CutoutString)

END

ELSE

INSERT INTO @returnResult (\[From\],\[To\]) VALUES(@Source,@Source)

RETURN

END

Source Code

另一个函数：

![279376ff13dddc6300e4ca52030b917b.png][]

![486058ce72a9c0cf31eea95b6a76a7a9.gif][]

![b7b5fc965ec0fdec0a920dca51a72ec4.gif][]

SET ANSI\_NULLS ON

SET QUOTED\_IDENTIFIER ON

\-- =============================================

\-- Author: Insus.NET

\-- Create date: 2019-05-27

\-- Update date: 2019-05-27

\-- Description: 两个字段合并为一个

CREATE FUNCTION \[dbo\].\[svf\_TwoFieldMergedToOne\] (

@field1 NVARCHAR(MAX),

@field2 NVARCHAR(MAX)

)

RETURNS NVARCHAR(MAX)

BEGIN

DECLARE @rtv NVARCHAR(MAX)

IF LEN(ISNULL(@field1,N'')) > 0 AND LEN(ISNULL(@field2,N'')) > 0

BEGIN

IF @field1 = @field2

SET @rtv = @field1

ELSE

SET @rtv = @field1 + N'-' + @field2

END

ELSE IF LEN(ISNULL(@field1,N'')) > 0 AND LEN(ISNULL(@field2,N'')) = 0

SET @rtv = @field1

ELSE IF LEN(ISNULL(@field1,N'')) = 0 AND LEN(ISNULL(@field2,N'')) > 0

SET @rtv = @field2

ELSE

SET @rtv = N''

RETURN @rtv

END

Source Code

最后实现如下：

![3fd38ccf1309d93a099bd8b0589c355d.png][]

![333b8532b0478b18c5c6e94d60f76099.gif][]

![d87e8589c71724d63f80c1c08864e864.gif][]

;WITH t AS

(

SELECT \[From\],\[To\] FROM \[dbo\].\[tvf\_SplitStringAsTwoField\](@Source)

)

SELECT \[From\],\[To\],\[dbo\].\[svf\_TwoFieldMergedToOne\](\[From\],\[To\]) AS \[Result\]

FROM t

Source Code

![b739ec46bb5c46d9c0aa4ce35ba1ea56.png][]

关于找一找教程网

本站文章仅代表作者观点，不代表本站立场，所有文章非营利性免费分享。

本站提供了软件编程、网站开发技术、服务器运维、人工智能等等IT技术文章，希望广大程序员努力学习，让我们用科技改变世界。

\[连续数字使用连接符替换\]http://www.zyiz.net/tech/detail-111901.html

[922f7d35e58272b01e20e21a989b43f5.png]: /images/20221022/5b07331203a9404daabe82decec81648.png
[0ddc4147c8f04382ca881c6eedd15634.png]: /images/20221022/87e2fd6ac3cb4769b14b413c0f07835d.png
[d163a4346f648c7d7fcfd8c086befbc7.gif]: https://img-blog.csdnimg.cn/img_convert/d163a4346f648c7d7fcfd8c086befbc7.gif
[a5a6c5537303c06270c4fe3a4338aa60.gif]: https://img-blog.csdnimg.cn/img_convert/a5a6c5537303c06270c4fe3a4338aa60.gif
[279376ff13dddc6300e4ca52030b917b.png]: https://img-blog.csdnimg.cn/img_convert/279376ff13dddc6300e4ca52030b917b.png
[486058ce72a9c0cf31eea95b6a76a7a9.gif]: https://img-blog.csdnimg.cn/img_convert/486058ce72a9c0cf31eea95b6a76a7a9.gif
[b7b5fc965ec0fdec0a920dca51a72ec4.gif]: https://img-blog.csdnimg.cn/img_convert/b7b5fc965ec0fdec0a920dca51a72ec4.gif
[3fd38ccf1309d93a099bd8b0589c355d.png]: https://img-blog.csdnimg.cn/img_convert/3fd38ccf1309d93a099bd8b0589c355d.png
[333b8532b0478b18c5c6e94d60f76099.gif]: https://img-blog.csdnimg.cn/img_convert/333b8532b0478b18c5c6e94d60f76099.gif
[d87e8589c71724d63f80c1c08864e864.gif]: https://img-blog.csdnimg.cn/img_convert/d87e8589c71724d63f80c1c08864e864.gif
[b739ec46bb5c46d9c0aa4ce35ba1ea56.png]: https://img-blog.csdnimg.cn/img_convert/b739ec46bb5c46d9c0aa4ce35ba1ea56.png