Selecting First String from Record SQL

Question

I have a query which so far returns the following;

Stock Code  BomReference        Description
2134601A    5134601A         ***DISC*** 004601 EXP Pack I PC Spoo (NF) 500MLX6
2134601A    5109052          40010934 IPC2101 UK PACK  PC SHAMPOO (NF) 500MLX6
2134601A    5134601B         40010908 004601 EXP PACK  PC SHAMPOO 500MLX6
2134601A    5109052L         40010909 IPC2101L UK PACK IPC SPOO  500ML X 6

The code is as follows;

SELECT     BomComponents.StockCode, BomHeaders.BomReference, BomHeaders.Description
FROM         BomComponents INNER JOIN
                      BomHeaders ON BomComponents.HeaderID = BomHeaders.ID
WHERE StockCode = '2134601A'

I want to be able to select just the first word/number from Description and then group the bom reference and Description together to result in the following.

StockCode        BomReference                                Description
 2134601A        5134601A, 5109052, 5134601B, 5109052L        ***DISC***, 40010934, 40010908, 40010909

Any help would be massively helpful.

See http://stackoverflow.com/questions/273238/how-to-use-group-by-to-concatenate-strings-in-sql-server — Ruud Helderman, Jul 08 '14 at 16:02
And to get the first word of a description, see http://stackoverflow.com/questions/707610/extract-the-first-word-of-a-string-in-a-sql-server-query — Ruud Helderman, Jul 08 '14 at 16:36

score 1 · Accepted Answer · answered Jul 08 '14 at 17:14

Creating the function below should accomplish this for you. I use it VERY frequently.

select dbo.getTokenValue([Your String], [Delimiting Character], [Position])

In this case: select dbo.getTokenValue(Description, ' ', 1)

/****** Object:  UserDefinedFunction [dbo].[getTokenValue]    Script Date: 7/8/2014 1:08:08 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


/*
 Accepts the string, delimeter and the position of the required value and returns the value 
*/

create function [dbo].[getTokenValue] (@tokenvalue varchar(200), @Delimeter char, @pos int)
returns varchar(200)
Begin
    Declare @DelimPos int
    Declare @remSubstr varchar(200)
    Declare @FinalStr varchar(200)
    Declare @Count int 
    Declare @Countdelim int

    set @Finalstr = ''
    Set @Countdelim = 0
    Set @remSubstr = @tokenValue
    Set @Count = @pos-1
    set @countdelim = 1

    while @Count <> 0 
        Begin
            Set @DelimPos = charindex(@Delimeter,@remSubstr) 
            If @DelimPos = 0
                Break;
                set @remSubstr = substring(@remSubstr,@DelimPos+1,Len(@remSubstr)-@DelimPos) 
                set @Count = @Count -1
                set @CountDelim = @CountDelim + 1
        End

    If @Pos > @CountDelim
        Begin       
            set @Finalstr = null
            return @FinalStr
        end
    else
        Begin
            Set @DelimPos = charindex(@Delimeter,@remSubstr) 
            if @DelimPos = 0
                Set @Finalstr = @remsubstr
            else 
                Set @FinalStr = substring(@remSubstr,1,@DelimPos-1)
        end

    return @FinalStr
end

GO

Selecting First String from Record SQL

1 Answers1