12,953,937 members (42,927 online)
Rate this:
See more:
Another one for you guys, i have this on oracle:
SELECT SUBSTR('JOHN SCOTT JUNIOR',1,instr('JOHN SCOTT JUNIOR',' ',1,1) )
FROM dual;
SELECT SUBSTR('JOHN SCOTT JUNIOR',instr('JOHN SCOTT JUNIOR',' ',-1,2)+1,instr('JOHN SCOTT JUNIOR',' ',-1,1)-instr('JOHN SCOTT JUNIOR',' ',-1,2) )
FROM dual;
SELECT SUBSTR('JOHN SCOTT JUNIOR',INSTR('JOHN SCOTT JUNIOR',' ',-1,1)+1 )
FROM dual;

RESULTS
SUBSTR('JOHNSCOTTJUNIOR',1,INSTR('JOHNSCOTTJUNIOR','',1,1))
-----------------------------------------------------------
JOHN

SUBSTR('JOHNSCOTTJUNIOR',INSTR('JOHNSCOTTJUNIOR','',-1,2)+1,INSTR('JOHNSCOTTJUNIOR','',-1,1)-INSTR('JOHNSCOTTJUNIOR','',-1,2))
------------------------------------------------------------------------------------------------------------------------------
SCOTT

SUBSTR('JOHNSCOTTJUNIOR',INSTR('JOHNSCOTTJUNIOR','',-1,1)+1)
------------------------------------------------------------
JUNIOR

I need to do this exact thing on SQL SERVER can anyone point me in the right direction?
Posted 3-May-11 7:07am

Rate this:

## Solution 1

PATINDEX[^] should be the T-SQL string function you're looking for!

Best Regards,

-MRB
Rate this:

## Solution 2

Well guys heres what i have so far, for my needs it works:
SELECT
left(substring('JOHN SCOTT JUNIOR',1,CHARINDEX(' ','JOHN SCOTT JUNIOR',1)),15) [first name],
substring('JOHN SCOTT JUNIOR',CHARINDEX(' ','JOHN SCOTT JUNIOR',1)+1,15) [first name]

RESULTS
FIRST_NAME    LAST_NAME
JOHN          SCOTT JUNIOR

Still working on the middle one, gonna leave it here if someone wants to take it and continue... it works but for some weird cases it doesn't display middle names...
substring('JOHN SCOTT JUNIOR',CHARINDEX(' ','JOHN SCOTT JUNIOR',CHARINDEX(' ','JOHN SCOTT JUNIOR')),LEN('JOHN SCOTT JUNIOR')+15-CHARINDEX(' ','JOHN SCOTT JUNIOR',CHARINDEX(' ','JOHN SCOTT JUNIOR'))-CHARINDEX(' ',REVERSE('JOHN SCOTT JUNIOR'),1)+1) [Last name],
REVERSE(substring(REVERSE( LEFT('JOHN SCOTT JUNIOR',LEN('JOHN SCOTT JUNIOR')+1-CHARINDEX(' ',REVERSE('JOHN SCOTT JUNIOR'),1))),1,CHARINDEX(' ',REVERSE( LEFT('JOHN SCOTT JUNIOR',LEN('JOHN SCOTT JUNIOR')+1-CHARINDEX(' ',REVERSE('JOHN SCOTT JUNIOR'),1))),1)-1))    [last name],
REVERSE(substring(REVERSE('JOHN SCOTT JUNIOR'),1,CHARINDEX(' ',REVERSE('JOHN SCOTT JUNIOR'),1)-1))  [second last name],
v3

Top Experts
Last 24hrsThis month
 ppolymorphe 100 OriginalGriff 75 RickZeeland 60 Dave Kreskowiak 38 Member 13197019 35
 OriginalGriff 6,464 CHill60 3,490 Maciej Los 3,123 ppolymorphe 2,020 Jochen Arndt 1,975