How do I parse 2 value in 1 field

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

How do I parse 2 value in 1 field

Author

Topic

Monhana
Starting Member

5 Posts

Posted - 2013-07-30 : 16:50:32

Hi SQL guru,

I have field First Name that contain FirstName and Middle Name Like:

Jon
O.B Jack
J. Michael
Grey. J

Is there any syntax that Can stripe out Jon is first name. O.B is first name J is first name, Grey is first name

Jack is middle name, Michael is middle name, and J is middle name

Thanks and Regards,

Katie

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-30 : 17:08:04

There isn't really anything that will parse it reliabily - mainly because it looks like there can be so many variations to the names. If you can state definite rules on the format and the separators, you can write queries that will parse it. Here is an example to get you started. You can copy this code and run it from SSMS to see what it does

CREATE TABLE #tmp(NAME VARCHAR(32));

INSERT INTO #tmp VALUES ('Jon'),
('O.B Jack'),
('J. Michael'),
('Grey. J')

SELECT
	RTRIM(LTRIM(LEFT(name,CHARINDEX(' ',name+' ')))) AS FirstName,
	LTRIM(RTRIM(STUFF(name,1,CHARINDEX(' ',name+' '),''))) AS MiddleName
FROM
	#tmp;

DROP TABLE #tmp;

Monhana
Starting Member

5 Posts

Posted - 2013-08-01 : 15:06:23

Ohh my goodness , it is working. Thank You very much for your help.

Can you help me out with syntax that combine into the same code to make First Name, Middle name is upcase (basically right now in the input file is lower case , I want both firstname and middname is Upper case.

Thanks

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-08-01 : 15:11:57

[CODE]
-- James K's code modified:
CREATE TABLE #tmp(NAME VARCHAR(32));

INSERT INTO #tmp VALUES ('Jon'),
('O.B Jack'),
('J. Michael'),
('Grey. J')

SELECT
UPPER(RTRIM(LTRIM(LEFT(name,CHARINDEX(' ',name+' '))))) AS FirstName,
UPPER(LTRIM(RTRIM(STUFF(name,1,CHARINDEX(' ',name+' '),'')))) AS MiddleName
FROM
#tmp;

DROP TABLE #tmp;

[/CODE]

quote:
Originally posted by Monhana

Ohh my goodness , it is working. Thank You very much for your help.

Can you help me out with syntax that combine into the same code to make First Name, Middle name is upcase (basically right now in the input file is lower case , I want both firstname and middname is Upper case.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-02 : 01:29:09

quote:
Originally posted by Monhana

Ohh my goodness , it is working. Thank You very much for your help.

Can you help me out with syntax that combine into the same code to make First Name, Middle name is upcase (basically right now in the input file is lower case , I want both firstname and middname is Upper case.

Thanks

why do you want to do this in sql?
its a presentation issue that can be dealt with in front end using formatting functions.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Monhana
Starting Member

5 Posts

Posted - 2013-08-12 : 16:02:58

Thank you very much for your response.

Is there any way that i can get output without period like :

First Name: Grey. will be Grey
Middle Name J. will be J
or middle name : William will be full William.

basiclly if intial in first name or middle name, need to get out period.

Thanks and Regards.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-12 : 16:18:48

quote:
Originally posted by Monhana

Thank you very much for your response.

Is there any way that i can get output without period like :

First Name: Grey. will be Grey
Middle Name J. will be J
or middle name : William will be full William.

basiclly if intial in first name or middle name, need to get out period.

Thanks and Regards.

Do this:

-- MuMu88's code modified:
-- James K's code modified:
CREATE TABLE #tmp(NAME VARCHAR(32));

INSERT INTO #tmp VALUES ('Jon'),
('O.B Jack'),
('J. Michael'),
('Grey. J')

SELECT
	CASE WHEN FirstName LIKE '%.' THEN LEFT(Firstname,LEN(FirstName)-1) ELSE FirstName END AS FirstName,
	CASE WHEN MiddleName LIKE '%.' THEN LEFT(MiddleName,LEN(MiddleName)-1) ELSE MiddleName END AS MiddleName
FROM
(
	SELECT
		UPPER(RTRIM(LTRIM(LEFT(name,CHARINDEX(' ',name+' '))))) AS FirstName,
		UPPER(LTRIM(RTRIM(STUFF(name,1,CHARINDEX(' ',name+' '),'')))) AS MiddleName
	FROM
		#tmp
) s

DROP TABLE #tmp;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources