12,360,321 members (50,114 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

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Top Experts
Last 24hrsThis month
 Richard MacCutchan 300 OriginalGriff 233 Dave Kreskowiak 205 Karthik Bangalore 180 RyanDev 115
 Richard MacCutchan 260 OriginalGriff 233 Dave Kreskowiak 205 Karthik Bangalore 180 RyanDev 115

Advertise | Privacy | Mobile
Web02 | 2.8.160621.1 | Last Updated 4 May 2011