oracle instr equivalent in sql server 2008

Question

4.50/5 (2 votes)

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

rhapemerald

Add a Solution

2 solutions

Solution 1

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

Best Regards,

-MRB

Posted 3-May-11 7:38am

Manfred Rudolf Bihy

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

rhapemerald · Accepted Answer · 2011-05-03T11:51:00

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],