SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 How do I parse 2 value in 1 field
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Monhana
Starting Member

5 Posts

Posted - 07/30/2013 :  16:50:32  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 07/30/2013 :  17:08:04  Show Profile  Reply with Quote
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;
Go to Top of Page

Monhana
Starting Member

5 Posts

Posted - 08/01/2013 :  15:06:23  Show Profile  Reply with Quote
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
Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 08/01/2013 :  15:11:57  Show Profile  Reply with Quote

-- 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;
 
 



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

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 08/02/2013 :  01:29:09  Show Profile  Reply with Quote
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
Go to Top of Page

Monhana
Starting Member

5 Posts

Posted - 08/12/2013 :  16:02:58  Show Profile  Reply with Quote
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.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 08/12/2013 :  16:18:48  Show Profile  Reply with Quote
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;
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000