How to find space before comman(,)

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

General SQL Server Forums

New to SQL Server Programming

How to find space before comman(,)

Author

Topic

divya317
Starting Member

16 Posts

Posted - 2008-09-17 : 17:12:51

Hi All

I have to search those names who have space b/w there last name and name format is 'LastName,FirstName MiddleName'. So after comman there is first name & middle name. I have to identify those name who has space b/w lastname? how can find those?

thanks!!!

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2008-09-17 : 17:33:22

use charindex to find the location

divya317
Starting Member

16 Posts

Posted - 2008-09-17 : 17:44:16

but it will count all the space before and after comman.
Suppose name is like 'RUSCH BROWN, JESSICA MARIE' then charindex will find all the spaces.I am not sure!!

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2008-09-17 : 17:58:45

Well, what exactly are you trying to achieve ?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-09-17 : 18:07:02

Maybe:[CODE]SELET *
FROM MyTable
WHERE MyColumn LIKE '% ,%'

-- OR
SELET *
FROM MyTable
WHERE MyColumn LIKE '%' + CHAR(32) + ',%'[/CODE]

soorajtnpki
Posting Yak Master

231 Posts

Posted - 2008-09-18 : 01:45:18

HI Divya
try this out,
I think there is space b/w firstname,lastname,middlename

select * FROM table WHERE CHARINDEX(' ',ltrim(rtrim(name)),CHARINDEX(' ', ltrim(rtrim(name)),1)+1) >0

ok tanx...

divya317
Starting Member

16 Posts

Posted - 2008-09-18 : 10:08:38

Thanks a lot for every one's reply
But I havent achive my pupose. Actully in table, I have a column O_FullName and from the full name column I have to distribute FName,MName,LName. Now in O_FullName has a format like LFM ie. lastname,firstname,middelname. FirstName and LAstName is saperated by comman(,) i.e if offender fullname is 'RUSCH BROWN, JESSICA MARIE' then before comman every think is last name ;LName:'RUSCH BROWN' and after comman(,) fname :JESSICA and after space lastname :'MARIE'
I hope , I am able to explain my purpose.

Thanks,
Divya

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-18 : 10:12:48

quote:
Originally posted by divya317

Thanks a lot for every one's reply
But I havent achive my pupose. Actully in table, I have a column O_FullName and from the full name column I have to distribute FName,MName,LName. Now in O_FullName has a format like LFM ie. lastname,firstname,middelname. FirstName and LAstName is saperated by comman(,) i.e if offender fullname is 'RUSCH BROWN, JESSICA MARIE' then before comman every think is last name ;LName:'RUSCH BROWN' and after comman(,) fname :JESSICA and after space lastname :'MARIE'
I hope , I am able to explain my purpose.

Thanks,
Divya

how will person have two last names? RUSCH BROWN & MARIE?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-18 : 10:18:02

ok...read your first post. i think what you want is this

SELECT PARSENAME(REPLACE(O_FullName,',','.'),2) AS LastName,
COALESCE(PARSENAME(REPLACE(PARSENAME(REPLACE(O_FullName,',','.'),1),' ','.'),2),PARSENAME(REPLACE(PARSENAME(REPLACE(O_FullName,',','.'),1),' ','.'),1)) AS FirstName,
CASE WHEN PARSENAME(REPLACE(PARSENAME(REPLACE(O_FullName,',','.'),1),' ','.'),2) IS NOT NULL THEN PARSENAME(REPLACE(PARSENAME(REPLACE(O_FullName,',','.'),1),' ','.'),1) ELSE NULL END AS MiddleName
FROM YourTable

i'm assume you always have last & firstnames

divya317
Starting Member

16 Posts

Posted - 2008-09-18 : 10:24:32

oh that was my typeing mistake 'MARIE' is middle name

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources