SQL - in a table a column is having value like 'Steven Kovy'. without using concat() function and concaten

GeekInterview.com > Interview Questions > Oracle > SQL

Go To First | Previous Question | Next Question

SQL | Question 137 of 173

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

Total Answers and Comments: 4

Last Update: January 06, 2010 Asked by: us.arvindpriya

Sponsored Links

Best Rated Answer

—

Submitted by: ashishkumar2403

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

Above answer was rated as good by the following members:
abhi_only12000

September 23, 2007 07:38:12

Enrique

RE: in a table a column is having value like 'Steven K...

/*
* 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 ' '

Is this answer useful? Yes | No

June 24, 2008 11:25:17

rahul.katke

Member Since: December 2007 Contribution: 3

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

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.

Is this answer useful? Yes | No

January 04, 2010 07:11:17

ashishkumar2403

Member Since: September 2009 Contribution: 6

RE: 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 RPAD(SUBSTR(name INSTR(name ' ' 1)+1) LENGTH(name) SUBSTR(name 1 INSTR(name ' ' 1))) FROM emp2;

Is this answer useful? Yes | No

1 0

Overall Rating: +1

January 06, 2010 04:56:44

hmounir

Member Since: November 2009 Contribution: 12

RE: 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 SUBSTR('Steven Kovy' INSTR('Steven Kovy' ' ' 1)+1)
SUBSTR('Steven Kovy' 1 INSTR('Steven Kovy' ' ' 1)) FROM dual;

Is this answer useful? Yes | No

Go To Top

User Name		Remember Me?
Password

Question Bank