-2

Input: logic to get o/p:Before open bracket '(' will be fname,inside () and after @ will be alias1 andalias2 depending on sequence order.

Full Name
------------------
ABC (PQR)
(ABC) PQR (XYZ)
ABC @ PQR
ABC @ PQR (XYZ)
ABC (PQR) @123

Desired output:

FNAME   ALIAS1   ALIAS2
-----------------------
ABC     PQR
PQR     ABC      XYZ
ABC     PQR
ABC     PQR      XYZ
ABC     PQR      123
Aryan Sena
  • 170
  • 2
  • 2
  • 12
  • After formatting your code I am still not wiser as to what you want. Please read https://stackoverflow.com/help/how-to-ask and https://stackoverflow.com/help/mcve and give SO enough context to help you out.. SO does not write your querys for you, post what you got, where it differs and what you want – Patrick Artner Jan 03 '18 at 11:21
  • Could someone have more than 3 parts? For example: `ABC (QPR) @ DEF (TEY)`. – Thom A Jan 03 '18 at 11:22
  • I removed the incompatible database tags. Please tag your question with the database you are really using. – Gordon Linoff Jan 03 '18 at 11:23
  • if you are after splitting strings, have a look at @GordonLinoff s answer here: https://stackoverflow.com/a/26878365/7505395 which would make this question a duplicate of https://stackoverflow.com/questions/26878291/split-string-by-delimiter-position-using-oracle-sql – Patrick Artner Jan 03 '18 at 11:25
  • 1
    Which [DBMS](https://en.wikipedia.org/wiki/DBMS) product are you using? Postgres? Oracle? "SQL" is just a query language, not the name of a specific database product. –  Jan 03 '18 at 11:31

1 Answers1

0

Please use something like this-

;WITH CTE1 AS
(
    SELECT REPLACE(REPLACE(REPLACE([Full Name],'(',''),')',''),'@','') [Full Name] FROM Names
)
,CTE AS
(
    SELECT [Full Name],
    CAST(('<r><n>' + REPLACE([Full Name],' ', '</n><n>') + '</n></r>') AS XML) X
   FROM CTE1
)
SELECT 
FNAME, CASE WHEN ALIAS1 = '' THEN ALIAS2 ELSE ALIAS1 END ALIAS1,
CASE WHEN ALIAS1 = '' THEN '' ELSE ALIAS2 END ALIAS2 
FROM 
(
    SELECT
     i.value('n[1]','VARCHAR(50)') AS FNAME,
     ISNULL(i.value('n[2]','VARCHAR(50)'),'') AS ALIAS1,
     ISNULL(i.value('n[3]','VARCHAR(50)'),'') AS ALIAS2
    FROM CTE c
    CROSS APPLY c.X.nodes('/r') x(i)
)z

OUTPUT

/*------------------------
OUTPUT
------------------------*/
FNAME                        ALIAS1    ALIAS2
---------------------------- --------- --------------------------------------------------
ABC                          PQR       
ABC                          PQR       XYZ
ABC                          PQR       
ABC                          PQR       
ABC                          PQR       123

(5 row(s) affected)
Pawan Kumar
  • 1,991
  • 10
  • 12