Find Jobs
Hire Freelancers

Native SQL to Parse Prefix First Middle Last Suffix from full Name without platform dependent functions

$10-30 USD

已关闭
已发布将近 7 年前

$10-30 USD

货到付款
Parse a full name in the format, prefix first middle last suffix. Not all parts may be included. Most commonly there may be no middle name, prefix, or suffix. I want to be able to accomplish this in SQL not using any platform dependent functions so it can be a platform independent bridge. I tried some code but the suffix Jr. gets stuffed in with the last name. It may be that a web service for data quality or machine learning like Melissa would work better. The example includes data. SELECT FIRST_NAME.INPUT_DATA ,[login to view URL] ,FIRST_NAME.FIRST_NAME ,CASE WHEN 0 = CHARINDEX(' ',[login to view URL]) THEN NULL --no more spaces found, consider remaining to be last name ELSE SUBSTRING( [login to view URL] ,1 ,CHARINDEX(' ',[login to view URL])-1 ) END AS MIDDLE_NAME ,SUBSTRING( [login to view URL] ,1 + CHARINDEX(' ',[login to view URL]) ,LEN([login to view URL]) ) AS LAST_NAME FROM ( SELECT [login to view URL] ,CASE WHEN 0 = CHARINDEX(' ',[login to view URL]) THEN [login to view URL] --no space found, return the entire string ELSE SUBSTRING( [login to view URL] ,1 ,CHARINDEX(' ',[login to view URL])-1 ) END AS FIRST_NAME ,CASE WHEN 0 = CHARINDEX(' ',[login to view URL]) THEN NULL --no spaces found, consider to be first name ELSE SUBSTRING( [login to view URL] ,CHARINDEX(' ',[login to view URL])+1 ,LEN([login to view URL]) ) END AS REMAINING ,PREFIX.INPUT_DATA FROM ( SELECT --CLEAN_DATA --if first three characters match list, --parse as a "PREFIX". else return NULL for PREFIX. CASE WHEN SUBSTRING(CLEAN_DATA.FULL_NAME,1,3) IN ('MR ','MS ','DR ','MRS') THEN LTRIM(RTRIM(SUBSTRING(CLEAN_DATA.FULL_NAME,1,3))) ELSE NULL END AS PREFIX ,CASE WHEN SUBSTRING(CLEAN_DATA.FULL_NAME,1,3) IN ('MR ','MS ','DR ','MRS') THEN LTRIM(RTRIM(SUBSTRING(CLEAN_DATA.FULL_NAME,4,LEN(CLEAN_DATA.FULL_NAME)))) ELSE LTRIM(RTRIM(CLEAN_DATA.FULL_NAME)) END AS REMAINING ,CLEAN_DATA.INPUT_DATA FROM ( SELECT --trim leading & trailing spaces to prepare for processing --replace extra spaces in name REPLACE(REPLACE(LTRIM(RTRIM(FULL_NAME)),' ',' '),' ',' ') AS FULL_NAME ,FULL_NAME AS INPUT_DATA FROM ( --test with test data, or table --table --SELECT CONTACT AS FULL_NAME --FROM CONTACT --test data --/* SELECT 'Andy D Where' AS FULL_NAME UNION SELECT 'Cathy T Landers' AS FULL_NAME UNION SELECT 'Ms Annie Wint There' AS FULL_NAME UNION SELECT 'Frank Fields' AS FULL_NAME UNION SELECT 'Howdy U Pokes Jr.' AS FULL_NAME --*/ ) SOURCE_DATA ) CLEAN_DATA ) PREFIX ) FIRST_NAME
项目 ID: 13825071

关于此项目

4提案
远程项目
活跃7 年前

想赚点钱吗?

在Freelancer上竞价的好处

设定您的预算和时间范围
为您的工作获得报酬
简要概述您的提案
免费注册和竞标工作
4威客以平均价$25 USD来参与此工作竞价
用户头像
Hello, I am SQL developer having more than 8 years of experience. I can give you solution and I am available during your time. Please give me sample data and expected result and I will definitely guve you solution. Regards, Shree
$25 USD 在1天之内
5.0 (1条评论)
0.2
0.2
用户头像
I have 11.8 years of experience on SQL DBA and T-SQL. I am suitable for this job to be completed with in given duration.
$25 USD 在1天之内
0.0 (0条评论)
0.0
0.0

关于客户

UNITED STATES的国旗
SAN ANGELO, United States
5.0
273
付款方式已验证
会员自7月 28, 2008起

客户认证

谢谢!我们已通过电子邮件向您发送了索取免费积分的链接。
发送电子邮件时出现问题。请再试一次。
已注册用户 发布工作总数
Freelancer ® is a registered Trademark of Freelancer Technology Pty Limited (ACN 142 189 759)
Copyright © 2024 Freelancer Technology Pty Limited (ACN 142 189 759)
加载预览
授予地理位置权限。
您的登录会话已过期而且您已经登出,请再次登录。