代码之家 › 专栏 › 技术社区 › Developer

如何拆分字符串并用数字、文本和其他条件分隔数据

sql

Developer · 技术社区 · 3 年前

我有以下一段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

0 回复 | 直到 3 年前

Jonathan Willcock 3 年前

您可以添加第三列:

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),在这种情况下,第一个是大写,第二个是小写。

此外,您的数据与您的要求不符:肝素Sod Inj USP 30000 uni应该发生什么?我现在需要出去。