How to split string and separate the data with numbers and text and other condition

user_388388 0
Viewed 138

I have the following piece of sql code

DECLARE @str VARCHAR(MAX) = 'CALCITRIOL 0.25mcg CAPSULE (EA' 

select 
case patindex('%[0-9]%', @str)
    when 0 then @str
    else left(@str, patindex('%[0-9]%', @str) -1 ) 

 

end, stuff(stuff(@str+'x', patindex('%[0-9][^0-9.]%', @str+'x') + 1, len(@str), ''
                  ), 1, patindex('%[0-9]%', @str) - 1, '')

The current output is displaying as follows

 CALCITROL     0.25

I would like to get the other data so that it should display data as follows

 CALCITROL     0.25  mcg  capsule (EA

Another example of string can be GENTAMICIN OPT SOL 5ML EACH, the current query gives me

GENTAMICIN OPT SOL 5

Expected is

GENTAMICIN OPT SOL 5 ML EACH

Here is the sql fiddle

http://sqlfiddle.com/#!18/9eecb/132229/0

sql-server sql
1 Answers

You could add a third column:

case patindex('%[0-9]%', REVERSE(@str))
    when 0 then ''
    else REVERSE(left(REVERSE(@str), patindex('%[0-9]%', REVERSE(@str)) -1 )) END

This gets most of the way there:

declare @fullstr table (bigstr varchar(100) COLLATE Latin1_General_CS_AS);
INSERT INTO @fullstr
VALUES
('ARANESP 100mcg PFS SOLD BY THE'),('ARANESP 25mcg PFS SOLD BY THE'),('ARANESP 40mcg PFS SOLD BY THE'),
('ARANESP 60mcg PFS SOLD BY THE'),('CALCITRIOL 0.25mcg CAPSULE (EA'),('CALCITRIOL ORAL .50mcg EACH'),
('CEFAZOLIN SODIUM 1gm EACH MMS'),('CEFTAZIDIME INJ 1gm SDV "EACHE'),('CINACALCET HCL 30mg 30/BT SLAT'),
('CINACALCET HCL 60mg 30/BT 100'),('CINACALCET HCL 90mg 30/BT'),('EPOGEN 10 000/ML MDV 2ml "EA"'),
('EPOGEN 20 000 U/ML MDV 1ml "EA"'),('EPOGEN 2000U/ML 1ml "EA" SOLD'),('EPOGEN 3000U/ML 1ml "EA" SOLD'),
('EPOGEN 4000U/ML 1ml "EA" SOLD'),('GENTAMICIN OPT SOL 5ml EACH'),('GENTAMICIN SULFATE 0.1% CREAM'),
('GENTAMICIN SULFATE 80MG/2ml CT'),('Heparin Sod Inj USP 30 000 uni'),('LEVOFLOXACIN 250mg TAB EACH 50'),
('LEVOFLOXACIN TAB 500mg "SOLD A'),('LIDOCAINE/PRILOCAINE 2.5% 30GM'),('VANCOMYCIN FTV 1gm "EA" 10/BX'),
('VANCOMYCIN FTV 500mg "EA" 10/B'),('VENOFER 20MG/ML 5ml (100mg) EA'),('WATER STERILE FTV 10ml "EA" 25');

declare @replaces table (id int identity (100,1), units varchar(20) COLLATE Latin1_General_CS_AS);

INSERT INTO @replaces (units) VALUES ('Fluid Extract'),('Concentrate'),('Injectable'),('Suspension'),('tbu/0.1ml'),('62.5mg/ml'),
                                        ('Diaphragm'),('Emulsion'),('Crystals'),('Granules'),('Lollipop'),('ounce(s)'),('units/ml'),
                                        ('Tincture'),('Solution'),('Shampoo'),('Pudding'),('mg/10ml'),('5mcg/ml'),('2mcg/ml'),
                                        ('UNKNOWN'),('dose(s)'),('Lozenge'),('Implant'),('Inhaler'),('Aerosol'),('Device'),('lotion'),
                                        ('Leaves'),('Liquid'),('Elixir'),('Flakes'),('Insert'),('bottle'),('MCG/ml'),('scoops'),
                                        ('tbu/ml'),('MG/2ml'),('GM/5ml'),('MG/tab'),('Tampon'),('Pellet'),('Powder'),('Spirit'),
                                        ('Troche'),('UKNOWN'),('Wafer'),('Paste'),('Syrup'),('Strip'),('Sheet'),('patch'),
                                        ('spray'),('stick'),('units'),('mg/ml'),('liter'),('MG/kg'),('MG/m2'),('cream'),('L/min'),
                                        ('Beads'),('Enema'),('Film'),('Disk'),('Foam'),('tape'),('oint'),('mask'),('appl'),('gtts'),
                                        ('inch'),('MG/g'),('tbsp'),('supp'),('puff'),('Ring'),('Swab'),('Wax'),('Tar'),('tbu'),
                                        ('pkg'),('tab'),('tsp'),('bag'),('bar'),('can'),('cap'),('mcg'),('meq'),('Gas'),('Gel'),
                                        ('IUD'),('Kit'),('Gum'),('Oil'),('Pad'),('mg'),('ml'),('iu'),('gm'),('gr'),('%');

;WITH cte AS 
(SELECT REPLACE(bigstr, r.units, '##' + cast(r.id as varchar(3)) + '§§') translated
FROM @fullstr f INNER JOIN @replaces r On f.bigstr LIKE '%' + units + '%' 
WHERE  REPLACE(bigstr, r.units, '##' + cast(r.id as varchar(3)) + '§§') <> bigstr)

select 
case charindex('##'+CAST(r.id as char(3))+'§§', translated)
    when 0 then translated
    else REVERSE(SUBSTRING(REVERSE(left(translated, charindex('##'+CAST(r.id as char(3))+'§§', translated) -1 )), CHARINDEX(' ', REVERSE(left(translated, charindex('##'+CAST(r.id as char(3))+'§§', translated) -1 ))),100 ))
end, 
case charindex('##'+CAST(r.id as char(3))+'§§', translated)
    when 0 then translated
    else REVERSE(SUBSTRING(REVERSE(left(translated, charindex('##'+CAST(r.id as char(3))+'§§', translated) -1 )), 1, CHARINDEX(' ', REVERSE(left(translated, charindex('##'+CAST(r.id as char(3))+'§§', translated) -1 ))) ))
end, 

r.Units, SUBSTRING(c.translated, charindex('§§', c.translated) + 3, 100)
FROM cte c INNER JOIN @replaces r ON SUBSTRING(c.translated, charindex('##', c.translated) + 2, 3) COLLATE Latin1_General_CS_AS  = CAST(r.id AS CHAR(3)) COLLATE Latin1_General_CS_AS
ORDER BY translated

There remain problems: You need to observe case sensitivity in order to ensure that your dosages do not lead to joins in the wrong place. I have the dosages lower case unless it is a combo (eg MG/2ml) in which case the first is upper case and the second bit lower.

Also your data does not match your requirement: what is supposed to happen with Heparin Sod Inj USP 30 000 uni? I need to go out now.

user_79900320
Related