In a table a column is having value like 'Steven Kovy'. without using concat() function and concatenate operator "||" how to display like 'Kovy Steven'

Enrique

• Sep 23rd, 2007

 

/*
 * In a table a column is having value like 'Steven Kovy'. without using concat() function and
 * concatenate operator "||" how to display like 'Kovy Steven'
 */
SELECT STUFF(STUFF(FirstName,1,CHARINDEX(' ',FirstName) - 1,
                   SUBSTRING(FirstName,CHARINDEX(' ',FirstName) + 1,LEN(FirstName))),
             LEN(FirstName) - CHARINDEX(' ',FirstName) + 2,
             LEN(FirstName) - CHARINDEX(' ',FirstName),
             SUBSTRING(FirstName,0,CHARINDEX(' ',FirstName))) As ModifiedString
FROM   Emp
WHERE  FirstName LIKE '% %'

rahul.katke

 Profile Answers by rahul.katke 

• Jun 24th, 2008

 

Assuming you had a table called s_emp2 which is nothing but the emp table from scott schema...

The qry below should serve the purpose.

select rpad(rpad(substr(ename, instr(ename, ' ')+1), length(substr(ename, instr(ename, ' ')+1))+1, ' '), length(ename), substr(ename, 1, instr(ename, ' ')-1)) reversed from s_emp2;

Again, some assumptions:

1. there is only 1 space between Steven and Kovy mentioned above.
2. and the space mentioned in point 1 above is the only space in the string.

Log:

SQL> select ename, rpad(rpad(substr(ename, instr(ename, ' ')+1), length(substr(ename, instr(ename, ' ')+1))+1, ' '), length(ename), substr(ename, 1, instr(ename, ' ')-1)) reversed from s_emp2;

ENAME      REVERSED
---------- ------------------------------
55 SMITH   SMITH 55
ALLEN
WARD BOY   BOY WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS

ENAME      REVERSED
---------- ------------------------------
JAMES
FORD
MILLER
RAHUL K    K RAHUL

15 rows selected.

Elapsed: 00:00:00.90
SQL>

Similar such thing could also be achieved using LPAD.

Hope that helps!

- Rahul Katke.

ashishkumar2403

 Profile Answers by ashishkumar2403 

• Jan 4th, 2010

 

SELECT RPAD(SUBSTR(name,INSTR(name,' ',1)+1), LENGTH(name), SUBSTR(name,1,INSTR(name,' ',1))) FROM emp2;

hmounir

 Profile Answers by hmounir 

• Jan 6th, 2010

 

SELECT SUBSTR('Steven Kovy',INSTR('Steven Kovy',' ', 1)+1),
SUBSTR('Steven Kovy',1,INSTR('Steven Kovy', ' ', 1)) FROM dual;

yagneswarasankar

 Profile Answers by yagneswarasankar 

• Nov 23rd, 2010

 

  1* select rpad(lpad('Steven Kovy',(length('Steven Kovy') +1),''''),length('Steven Kovy')+2,'''')  Name  from dual
SQL> /

NAME
-------------
'Steven Kovy'

krishsidd

 Profile Answers by krishsidd 

• Nov 26th, 2010

 

Space included in name in answer submitted by ashishkumar2403


select Rpad(rpad(Substr('Steven Kovy',INSTR('Steven Kovy',' ')+1), length(Substr('Steven Kovy',INSTR('Steven Kovy',' ')+1))+1,' ' ), length('Steven Kovy'),substr('Steven Kovy',1,instr('Steven Kovy',' '))) x
from dual;