Solved

Substring and Index of in MSSQL 2005

Posted on 2009-04-03
3
9,152 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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Having clause with Case 2 32
MS SQL order by with "over" statement and row_number() 11 38
SQL Server 2012 express 24 36
Query to Add Late Tolerance 10 60
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
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…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.
I designed this idea while studying technology in the classroom.  This is a semester long project.  Students are asked to take photographs on a specific topic which they find meaningful, it can be a place or situation such as travel or homelessness.…

914 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now