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

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

SELECT [ID],[Strings],[Front],[Back] FROM #T
CROSS APPLY 
[dbo].[svf_CutOutStringWithHyphen]([Strings])