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
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;
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?
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