sql - TSQL:解析具有各种字符的字符串
问题描述
我有一个带有文件名列的表,其中各个供应商以不同的方式命名文件。因此,文件中存在一个文件名,其中包含姓氏、名字和中间名,并用各种字符分隔名称。有些有逗号+空格,逗号没有空格,单词之间有空格,单词之间没有空格,一个下划线,两个下划线等。
有哪些好的方法可以将其提取到所需的结果? (这是一次性的数据转换,不一定要漂亮。)
我尝试的是在下面的示例代码中,使用各种子字符串/字符索引组合
文件名示例:(注意逗号、空格、无空格、下划线、双下划线)
期望的结果:
示例代码/测试数据(在临时表中)
IF OBJECT_ID('tempdb..#dob') IS NOT NULL DROP TABLE #dob
CREATE TABLE #dob (
FILENAME VARCHAR(MAX)
,StudentID INT
,FullName VARCHAR(500)
,LastName VARCHAR(500)
,FirstName VARCHAR(500)
,MiddleName VARCHAR(500)
)
INSERT INTO #dob
( FILENAME )
VALUES
('Last, First, Middle_DOB ID.pdf')
,('Denver, John C 11_23_1980_123456.pdf')
,('Denver John_11-23-1980, 1234567.pdf')
,('Denver,John,Clifford_ 01_22_1980_123456.pdf')
,('Denver, John, 11-23-1980, 1234567.pdf')
,('Denver, John__01_22_1980_123456.pdf')
--This is what I tried.
SELECT FILENAME
,fullname
,LastName
,FirstName
,MiddleName
,SUBSTRING(FileName,1, CHARINDEX(' ', FileName, (charindex(' ', FileName, 1))+2)) AS test1
,SUBSTRING(FileName,1, CHARINDEX('_', FileName, (charindex(' ', FileName, 1))+2)) AS test2
,SUBSTRING(FileName,1, CHARINDEX(',', FileName, (charindex(', ', FileName, 1))+1)) AS test3
,SUBSTRING(FileName,1, CHARINDEX(' ', FileName, (charindex('__', FileName, 1))+2)) AS test4
,SUBSTRING(FileName,1, CHARINDEX('__', FileName, (charindex(' ', FileName, 1))+2)) AS test5
FROM #dob
解决方案
这是一个滑坡,但如果您的真实数据接近样本,请考虑以下事项。
例子
SELECT FILENAME
,LastName = Pos1
,FirstName = Pos2
,MiddleName = case when try_convert(int,left(Pos3,1)) is null then Pos3 else '' end
FROM #dob A
Cross Apply ( values ( replace(
replace(
replace(
replace(FileName,', ',',')
,' ,',',')
,' ',',')
,'_',',')
)
) B(CleanString)
Cross Apply [dbo].[tvf-Str-Parse-Row](CleanString,',') C
退货
FILENAME LastName FirstName MiddleName
Last, First, Middle_DOB ID.pdf Last First Middle
Denver, John C 11_23_1980_123456.pdf Denver John C
Denver John_11-23-1980, 1234567.pdf Denver John
Denver,John,Clifford_ 01_22_1980_123456.pdf Denver John Clifford
Denver, John, 11-23-1980, 1234567.pdf Denver John
Denver, John__01_22_1980_123456.pdf Denver John
有兴趣的TVF
CREATE FUNCTION [dbo].[tvf-Str-Parse-Row] (@String varchar(max),@Delimiter varchar(10))
Returns Table
As
Return (
Select Pos1 = ltrim(rtrim(xDim.value('/x[1]','varchar(max)')))
,Pos2 = ltrim(rtrim(xDim.value('/x[2]','varchar(max)')))
,Pos3 = ltrim(rtrim(xDim.value('/x[3]','varchar(max)')))
,Pos4 = ltrim(rtrim(xDim.value('/x[4]','varchar(max)')))
,Pos5 = ltrim(rtrim(xDim.value('/x[5]','varchar(max)')))
,Pos6 = ltrim(rtrim(xDim.value('/x[6]','varchar(max)')))
,Pos7 = ltrim(rtrim(xDim.value('/x[7]','varchar(max)')))
,Pos8 = ltrim(rtrim(xDim.value('/x[8]','varchar(max)')))
,Pos9 = ltrim(rtrim(xDim.value('/x[9]','varchar(max)')))
From ( values (cast('<x>' + replace((Select replace(@String,@Delimiter,'§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml))) as A(xDim)
)
推荐阅读
- ansible - 无法通过 ansible playbook 连接到远程服务器
- laravel-5 - 我已经通过 post 方法将图像文件存储在管理员中,但是当尝试为 pdf/word 放置相同的代码时,它不起作用
- jquery - jquery数据表每一行的下拉菜单
- mosquitto - MosquittoClient 的订阅者在间隔时间段内不显示单个订阅的消息
- javascript - 访问 Braintree 数组内部的问题
- android - 如何使这个设计成为android?
- java - 如何从列表视图内的线性布局中动态添加的视图中获取多个edittext值
- node.js - 如何使用heroku免费服务减少网页的TTFB
- intellij-idea - 为什么在 Intellij idea 中生成持久性映射时会为一对多/多对多关系创建额外的实体类?
- phpmyadmin - 在我的数据库中显示名称而不是 RFID 卡号