sql - 如何拆分字符串并用数字和文本等条件分隔数据

Question

我有以下一段 sql 代码

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, '')

当前输出显示如下

 CALCITROL     0.25

我想获取其他数据，以便它应显示数据如下

 CALCITROL     0.25  mcg  capsule (EA

字符串的另一个例子可以是 GENTAMICIN OPT SOL 5ML EACH，当前查询给了我

GENTAMICIN OPT SOL 5

预期是

GENTAMICIN OPT SOL 5 ML EACH

这是sql fiddle
http://sqlfiddle.com/#!18/9eecb/132229/0

Answer 1

您可以添加第三列:

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

这得到了大部分的方式:

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

仍然存在问题:您需要注意区分大小写，以确保您的剂量不会导致连接在错误的位置。我的剂量是小写的，除非它是一个组合(例如 MG/2ml)，在这种情况下，第一个是大写，第二个是小写。
此外，您的数据与您的要求不符:Heparin Sod Inj USP 30 000 uni 会发生什么？我现在需要出去。