Solved

Substring and Index of in MSSQL 2005

Posted on 2009-04-03
3
9,155 Views
Last Modified: 2012-05-06
Hi,

I am using MSSQL 2005

I have complete Names in table column of the database

I wanted to seperate firstname and lastname. They all are , seperated

I mean if I have Jain, Akalank

here 'Jain' is the last name and 'Akalank' is the first name

And more names are

Jain, Akalank
Soubigou, Alban
Plattner, Albert
Klatt, Alexander
Simon, Alex
David, Alexandre
Holness, Andrew
Evra, Antoine
Stracke, Armin
Pathammavong, Arom
Savaria, Benoit
Mueller, Boris
Abitbol-Zouari, Caroline
Eck, Christian
Pierce, Daniel
De Giorgio, Domenicantonio
Floridia, Emanuele
Davidson, Emma Louise
De Palma, Federico
Milio, Francesco
Ortone, Francesco
Melka, Frederic
Wilson, Glenn
Rodriguez San Pedro, Jaime
NULL

How can we substring these names in 2 columns

Please help me with query

Many Thanks
0
Comment
Question by:tia_kamakshi
3 Comments
 
LVL 12

Accepted Solution

by:
udayakumarlm earned 250 total points
ID: 24058720
use
select Left(FieldName,CharIndex(',', FieldName)-1) , Right(FieldName,Len(FieldName) -CharIndex(',', FieldName))  from tableName
0
 
LVL 23

Assisted Solution

by:adathelad
adathelad earned 250 total points
ID: 24058754
Hi,

Given this a quick test, and it works if the column contains nulls or records without a comma in them...
SELECT *,
	CASE 
		WHEN CHARINDEX(',', YourField) > 0 THEN
			LEFT(YourField, CHARINDEX(',', YourField) - 1)
		ELSE YourField
	END AS FirstPart,
	CASE 
		WHEN CHARINDEX(',', YourField) > 0 THEN
			LTRIM(SUBSTRING(YourField, CHARINDEX(',', YourField) + 1, LEN(YourField)))
		ELSE
		''
	END AS SecondPart
FROM YourTable

Open in new window

0
 

Author Closing Comment

by:tia_kamakshi
ID: 31566197
Many Thanks. Its great
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question