一些字符串是由"-"连接字符连接。
想以这个连接字符"-"对字符串时行截取前后字符。
可以写一个自定义函数:
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: Insus.NET -- Create date: 2019-05-27 -- Update date: 2019-05-27 -- Description: 截取字符串 -- ============================================= CREATE FUNCTION [dbo].[svf_CutOutStringWithHyphen] ( @string NVARCHAR(MAX) ) RETURNS @Table TABLE([Front] NVARCHAR(MAX),[Back] NVARCHAR(MAX)) AS BEGIN DECLARE @hyphen VARCHAR(1) = N'-' IF @string LIKE '%'+ @hyphen +'%' BEGIN DECLARE @hyphen_position INT = CHARINDEX(@hyphen, @string) INSERT INTO @Table ([Front],[Back]) SELECT SUBSTRING(@string, 1, @hyphen_position - 1) AS _From, SUBSTRING(@string, @hyphen_position + 1, LEN(@string)- @hyphen_position) AS _To END ELSE INSERT INTO @Table ([Front],[Back]) VALUES(N'',N'') RETURN END GO
例子:
下面有一临时表,存储一些字符:
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
CREATE TABLE #T ([ID] INT, [Strings] NVARCHAR(40)) INSERT INTO #T([ID],[Strings]) VALUES (1,'B-Q'),(2,'23-45'),(3,'H-P'),(4,'ADF'),(5,'ADSF-ASDF-ASDF'),(6,'-ADF-ADF-'),(7,'-SFDG-KLJ-QER-'),(8,'shg-'),(9,'-sdfgs') SELECT [ID],[Strings] FROM #T
现使用上面的自定义函数对临时表中[String]字符进行分割。得到的结果如下:
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
SELECT [ID],[Strings],[Front],[Back] FROM #T CROSS APPLY [dbo].[svf_CutOutStringWithHyphen]([Strings])