SUBSTRING Functions

Transact-SQL
1

Hello Everyone and good evening.

Could anyone help with this.

DECLARE @ListSplitName TABLE(Name VARCHAR(255))

INSERT INTO @ListSplitName
VALUES
('Inv 3225366, BUK-100694, London Distribution Solutions B, PICKING 37,531 @£0.20')

SELECT * FROM @ListSplitName;

I have tried using this function - substring(name , charindex(',',name)+2,LEN(name)) from FROM @ListSplitName but, is not doing the job

I only want BUK-00694 from the string.

Any help would be apprciated.

Thanks

2

You need to use 2 `CHARINDEX()` 

SELECT SUBSTRING(name, c1 + 1, c2 - c1 - 1)
FROM   @ListSplitName n
       CROSS APPLY
       (
           SELECT c1 = CHARINDEX(',', name)
       ) c1
       CROSS APPLY
       (
           SELECT c2 = CHARINDEX(',', name, c1 + 1)
       ) c2
4

Hi Harisgg1

Thank you much, much appreciated.

5

Hi Khtan

Thank you much, much appreciated.

6

Here is a version that splits the string on commas, then pulls the 2nd entry.
It works best on 2022+ when the ordinal option was added to STRING_SPLIT(), but I provided versions for 2016 and 2017 as well that use ROW_NUMBER()to generate the ordinal value.

SELECT lsn.Name
  , TRIM(s.value) AS [BUK]
  , s.ordinal
FROM ListSplitName lsn
  CROSS APPLY STRING_SPLIT(lsn.Name, ',', 1) s
WHERE s.ordinal = 2;

image

7

hi

hope this helps

solved = yes 

why am i doing this ? = tally table approach 
                      = among other reasons = one being neuro science

declare @t varchar(300) = 'Inv 3225366, BUK-100694, London Distribution Solutions B, PICKING 37,531 @£0.20'

;WITH Tally AS ( SELECT TOP (LEN(@t))  ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n FROM sys.objects)
SELECT 
   SUBSTRING(@t, n, CHARINDEX(',', @t + ',', n) - n) AS Value
FROM Tally
  WHERE SUBSTRING(',' + @t, n, 1) = ','
   AND 
 SUBSTRING(@t, n, CHARINDEX(',', @t + ',', n) - n) like '%BUK%';

image