SQL function, extract numbers before -

Question

Hi, i'm playing around with building a sql function that will extract numbers from a title, which is what the following code below does. Although, i want to modify this function to parse numbers into sections. For example:

Current Data in title field:

QW 1 RT 309-23-1
QW 1 RT 29-1
QW 1 RT 750-1
QW RT 750-1

Temp tables created once function is ran on title field:

column 1  Column 2  Column 3  Column 4
1         309       23        1
1         29        1         Null
1         750       1         Null
Null      750       1         Null

---

create function [dbo].[ExtractNumbers](@Numbers nvarchar(2000))  
returns nvarchar(2000)  
as 
BEGIN 
  declare @NonNumericIndex int  
  set @NonNumericIndex = PATINDEX('%[^0-9]%',@Numbers)  

  WHILE @NonNumericIndex > 0  
  begin 
    SET @Numbers = REPLACE(@Numbers,SUBSTRING(@Numbers,@NonNumericIndex,1),'')  
    SET @NonNumericIndex = PATINDEX('%[^0-9]%',@Numbers)  
    SET 
  end 

  return @Numbers  
END 

Answer 1

Here's one way.

Although actually at the end I realised the format was more fixed than I had originally realised so you may be better off just using the various string manipulation functions to calculate the columns directly.

WITH TestTable AS
(
SELECT 'QW 1 RT 309-23-1' AS title UNION ALL
SELECT 'QW 1 RT 29-1' UNION ALL
SELECT 'QW 1 RT 750-1' UNION ALL
SELECT 'QW RT 750-1' 
)
SELECT title, [1] AS [Column 1], [2] AS [Column 2],[3] AS [Column 3],[4] AS [Column 4]
FROM TestTable CROSS APPLY dbo.GetNumbers(title)
PIVOT 
(MAX(num) FOR idx IN ([1], [2],[3],[4])
) AS PivotTable;

Uses the following TVF

CREATE FUNCTION GetNumbers 
(
@Numbers NVARCHAR(2000) 
)
RETURNS @Results TABLE 
(
idx INT IDENTITY(1,1),
num INT
) 
AS
BEGIN
    DECLARE @NonNumericIndex INT,  @NumericIndex INT

    SET @NumericIndex = PATINDEX('%[0-9]%',@Numbers)

    IF (@NumericIndex > 4) --First Column not there
          INSERT INTO @Results VALUES (NULL)

    WHILE @NumericIndex > 0
    BEGIN
        SET @Numbers = RIGHT(@Numbers,LEN(@Numbers)-@NumericIndex+1)

        SET @NonNumericIndex = PATINDEX('%[^0-9]%',@Numbers)

        IF(@NonNumericIndex = 0)
        BEGIN
            INSERT
            INTO @Results VALUES (@Numbers)
            RETURN
        END
        ELSE
        INSERT
        INTO @Results VALUES
            (LEFT(@Numbers,@NonNumericIndex-1))

        SET @Numbers = RIGHT(@Numbers,LEN(@Numbers)-@NonNumericIndex+1)
        SET @NumericIndex = PATINDEX('%[0-9]%',@Numbers)
    END
    RETURN
END