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
3

hi

hope this helps

create data script

DROP TABLE #ListSplitName

CREATE TABLE #ListSplitName (Name VARCHAR(255))

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

SELECT 'Data',* FROM #ListSplitName

One way of doing this = STRING SPLIT

SELECT
value
FROM
#ListSplitName p
CROSS APPLY
STRING_SPLIT(Name, ',',1)
WHERE
ordinal = 2

Second way of doing this = CTE STUFF LEFT

; WITH cte AS
(
SELECT STUFF(name, 1, charindex(',',name), '') AS ok FROM #ListSplitName
)
SELECT
left(ok,charindex(',',ok)-1)
FROM
cte

– Third way of doing it

– Procedural ( but it may have its own benefits in Extremely narrow specific use case scenarios

DECLARE @delimiter varchar(1) = ','

DECLARE @FirstDelimiterPos INT ; 

SELECT 
   @FirstDelimiterPos = CHARINDEX(@delimiter, name) 
FROM 
  #ListSplitName

DECLARE @SecondDelimiterPos INT ;

SELECT 
    @SecondDelimiterPos = CHARINDEX(@delimiter, name, @FirstDelimiterPos + 1)
FROM 
  #ListSplitName

SELECT 
   SUBSTRING(name,@FirstDelimiterPos + 1, @SecondDelimiterPos - (@FirstDelimiterPos + 1))
FROM 
#ListSplitName

image
image778×160 4.8 KB

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