SUBSTRING Help

I have a string column that has descriptions like 'SODIUM CHLORIDE 0.9 % INTRAVENOUS SOLUTION_27838_250.000_mL'. I need to retrieve the following portion '27838' from each record. How can I achieve this ?

more examples of column:

FILGRASTIM 480 MCG/1.6 ML INJECTION SOLUTION_108074_1.600_mL
ACETAMINOPHEN 325 MG TABLET_101_100.000_Each
DIPHENHYDRAMINE 25 MG CAPSULE_2509_100.000_Each
FILGRASTIM 480 MCG/1.6 ML INJECTION SOLUTION_108074_1.600_mL
SODIUM CHLORIDE 0.9 % IV BOLUS 250 ML_410649_250.000_mL

In these blob of examples where does it start and end

Please provide this data as ddl and dml

Create table #farma

Insert into #farma

``

Hi

i tried to do this ..!! hope this helps :slight_smile:

please click arrow to the left for Drop Create Sample Data

Sample Data
DROP TABLE #Data
GO 

CREATE TABLE #data
(
String VARCHAR(200)
)
GO 

INSERT INTO #data SELECT 'ACETAMINOPHEN 325 MG TABLET_101_100.000_Each'
INSERT INTO #data SELECT 'DIPHENHYDRAMINE 25 MG CAPSULE_2509_100.000_Each'
INSERT INTO #data SELECT 'FILGRASTIM 480 MCG/1.6 ML INJECTION SOLUTION_108074_1.600_mL'
INSERT INTO #data SELECT 'SODIUM CHLORIDE 0.9 % IV BOLUS 250 ML_410649_250.000_mL'
GO

please click arrow to the left for SQL

SQL
;WITH [cte]
AS (
       SELECT
            String
            , LTRIM(RTRIM([m].[n].[value]('.[1]', 'varchar(8000)'))) AS [string_split]
       FROM
        (   SELECT
                String
                , CAST('<XMLRoot><RowData>' + REPLACE([#data].String, '_', '</RowData><RowData>')
                       + '</RowData></XMLRoot>' AS XML) AS [x]
            FROM
                [#data]) AS [t]
        CROSS APPLY [x].[nodes]('/XMLRoot/RowData') AS [m]([n])
   )
      , cte_rownum
AS (
       SELECT
             ROW_NUMBER() OVER (PARTITION BY
                                     String
                                 ORDER BY
                                     [string_split]) AS rn
            , String
            , [cte].[string_split]
       FROM
            [cte]
   )
SELECT  'SQL Output',* FROM  cte_rownum WHERE rn = 2;

image

Be careful about the order by when getting the rownumber. I changed one of the inserts to this
INSERT INTO #data SELECT 'ACETAMINOPHEN 325 MG TABLET_10_200.000_Each' and it returned the 200.000 instead of the 10. If the logic that Harish wrote is correct, meaning you want to find the first value between the underscores, then can you use a simple substring and charindex?

SELECT substring(String,CharIndex('_', String) + 1,charIndex('_', String, CharIndex('_', String) + 1) - CharIndex('_', String) - 1	)
		,* FROM #DATA
1 Like
select * 
  from #data
  cross apply DelimitedSplit8K(string,'_') x
  where x.ItemNumber = 2

1 Like

hi mike

INSERT INTO #data SELECT 'ACETAMINOPHEN 325 MG TABLET_10_200.000_Each' and it returned the 200.000 instead of the 10.

could you please explain WHY it does that !!
Thanks
:slight_smile:

It's because of how you were getting the row_number. The order by orders it by string_split. When it sorts, the 10 comes before the 200. Run this and you'll see the RN being produced

SELECT 'SQL Output',* FROM cte_rownum

1 Like

Thanks Mike! this actually worked for me!

I was able to use this approach on SQL server and our new warehouse Exasol. So thanks a bunch!