CODEWITHSUNDEEP
sqloracle-database
user2425416
user2425416

Reputation: 27

change the order of a string in pl/sql

I want to change the order of a string like : name/surname by surname/name OR name/ surname by surname/ name (taking account on spaces after/)

below my request.but the second part is wrong :

select SUBSTR ('name/surname' , INSTR ('name/surname','/')+1)  ||SUBSTR ('name/surname' , 1,INSTR('name/surname','/')-1) 
from dual

Upvotes: 0

Views: 1115

Answers (2)

Sandeep
Sandeep

Reputation: 806

select substr('firstname/lastname',instr('firstname/lastname','/')+1)||'/' || substr('firstname/lastname',1,instr('firstname/lastname','/')-1) from dual;

Upvotes: 0

MT0
MT0

Reputation: 167972

Using a regular expression:

SELECT REGEXP_REPLACE( 'name/surname', '^(.*?)/(.*)$', '\2/\1' ) FROM DUAL;

or trimming all whitespaces:

SELECT REGEXP_REPLACE( ' name / surname ', '^\s*(.*?)\s*/\s*(.*?)\s*$', '\2/\1' ) FROM DUAL;

or preserving a whitespace before and after the slash (and trimming leading/training whitespace):

SELECT REGEXP_REPLACE( ' name / surname ', '^\s*(.*?)(\s?/\s?)(.*?)\s*$', '\3\2\1' ) FROM DUAL;

Using string functions:

WITH names ( text ) AS (
  SELECT 'name/surname' FROM DUAL
)
SELECT SUBSTR( text, INSTR( text, '/' ) + 1 ) || '/' || SUBSTR( text, 1, INSTR( text, '/' ) - 1 )
FROM   names;

or trimming whitespace:

WITH names ( text ) AS (
  SELECT ' name / surname ' FROM DUAL
)
SELECT TRIM( SUBSTR( text, INSTR( text, '/' ) + 1 ) ) || '/' || TRIM( SUBSTR( text, 1, INSTR( text, '/' ) - 1 ) )
FROM   names;

Upvotes: 2

Related Questions