Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 9194
  • Last Modified:

Substring and Index of in MSSQL 2005

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
tia_kamakshi
Asked:
tia_kamakshi
2 Solutions
 
udaya kumar laligondlaTechnical LeadCommented:
use
select Left(FieldName,CharIndex(',', FieldName)-1) , Right(FieldName,Len(FieldName) -CharIndex(',', FieldName))  from tableName
0
 
adatheladCommented:
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
 
tia_kamakshiAuthor Commented:
Many Thanks. Its great
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now