如何从SQL中的给定字符串提取子字符串
问题描述:
我有应该分为名字,姓氏和中间名的全名。 以下是我的示例名称。如何从SQL中的给定字符串提取子字符串
1. WILLEMS, JAN MARIE J
2. HLAVACEK, PATRICIA K
3. KLIMEK, CHRISTOPHER
现在我的名字,中间名和姓氏应该是
Firstname Lastname Middlename
JAN MARIE WILLEMS J
PATRICIA HLAVACEK K
CHRISTOPHER KLIMEK
我试着用下面的查询。
declare @FullName varchar(100)='KLIMEK, CHRISTOPHER'
Select
LTRIM(RTRIM(SUBSTRING(@FullName, 0, CHARINDEX(',', @FullName)))) As LastName
,SUBSTRING(@FullName,CHARINDEX(' ',LTRIM(RTRIM(@FullName)))+1,(LEN(LTRIM(RTRIM(@FullName)))-CHARINDEX(' ',LTRIM(RTRIM(@FullName)))-CHARINDEX(' ',REVERSE(LTRIM(RTRIM(@FullName))))))
,SUBSTRING(@FullName , LEN(LTRIM(RTRIM(@FullName))) - CHARINDEX(' ',REVERSE(LTRIM(RTRIM(@FullName)))) + 2 , LEN(LTRIM(RTRIM(@FullName))))
当它没有中间名时,这不会给出正确的答案。
您能否提供您的意见?名
答
提取零件会导致混乱,但是这可能会做你的需要:
select
Firstname = left(rest,len(rest)-charindex(' ',reverse(rest)))
, Lastname
, Middlename = case
when charindex(' ',rest)>0
then right(rest,charindex(' ',reverse(rest))-1)
else ''
end
from (
select
Lastname = left(name, charindex(',',name+',')-1)
, rest = ltrim(rtrim(stuff(name, 1,charindex(',',name+','),'')))
from t
) as s
rextester演示:http://rextester.com/UOMM64478
回报:
+-------------+----------+------------+
| Firstname | Lastname | Middlename |
+-------------+----------+------------+
| JAN MARIE | WILLEMS | J |
| PATRICIA | HLAVACEK | K |
| CHRISTOPHER | KLIMEK | |
+-------------+----------+------------+
如果middlename
应始终只是一个中间首字母,长度为1,这将适应吃了:
select
Firstname = case
when charindex(' ',reverse(rest))=2
then left(rest,len(rest)-2)
else rest
end
, Lastname
, Middlename = case
when charindex(' ',reverse(rest))=2
then right(rest,charindex(' ',reverse(rest))-1)
else ''
end
, name
from (
select
Lastname = left(name, charindex(',',name+',')-1)
, rest = ltrim(rtrim(stuff(name, 1,charindex(',',name+','),'')))
, name
from t
) as s
回报:
+-------------+----------+------------+----------------------+
| Firstname | Lastname | Middlename | name |
+-------------+----------+------------+----------------------+
| JAN MARIE | WILLEMS | J | WILLEMS, JAN MARIE J |
| PATRICIA | HLAVACEK | K | HLAVACEK, PATRICIA K |
| CHRISTOPHER | KLIMEK | | KLIMEK, CHRISTOPHER |
| JAN MARIE | WILLEMS | | WILLEMS, JAN MARIE |
+-------------+----------+------------+----------------------+
什么SQL引擎和版本吗? 然后,你期望多首字母缩写吗? – gbn
SQL 2012.不是多个姓名缩写。名字可以有或没有首字母缩写 – chits
用逗号分隔,从前面移除空格然后按空格分隔。 – Snowlockk