CODEWITHSUNDEEP
sql
eatonphil
eatonphil

Reputation: 13682

SQL - Conditionally joining two columns in same table into one

I am working with a table that contains two versions of stored information. To simplify it, one column contains the old description of a file run while another column contains the updated standard for displaying ran files. It gets more complicated in that the older column can have multiple standards within itself. The table:

Old Column              New Column
Desc: LGX/101/rpt          null
  null                     Home
Print: LGX/234/rpt         null
  null                     Print
  null                     Page

I need to combine the two columns into one, but I also need to delete the "Print: " and "Desc: " string from the beginning of the old column values. Any suggestions? Let me know if/when I'm forgetting something you need to know!

(I am writing in Cache SQL, but I'd just like a general approach to my problem, I can figure out the specifics past that.)

EDIT: the condition is that if substr(oldcol,1,5) = 'desc: ' then substr(oldcol,6) else if substr(oldcol,1,6) = 'print: ' then substr(oldcol,7) etc. So as to take out the "desc: " and the "print: " to sanitize the data somewhat.

EDIT2: I want to make the table look like this:

    Col
  LGX/101/rpt
    Home
  LGX/234/rpt
    Print
    Page

Upvotes: 0

Views: 2102

Answers (3)

Prakash
Prakash

Reputation: 199

You can remove the Print: and Desc: by using a combination of CharIndex and Substring functions. Here it goes

SELECT  CASE WHEN CHARINDEX(':',COALESCE(OldCol,NewCol)) > 0 THEN 
         SUBSTRING(COALESCE(OldCol,NewCol),CHARINDEX(':',COALESCE(OldCol,NewCol))+1,8000) 
         ELSE
         COALESCE(OldCol,NewCol)
        END AS Newcolvalue  
FROM  [SchemaName].[TableName]

The Charindex gives the position of the character/string you are searching for.

So you get the position of ":" in the computed column(Coalesce part) and pass that value to the substring function. Then add +1 to the position which indicates the substring function to get the part after the ":". Now you have a string without "Desc:" and "Print:".

Hope this helps.

Upvotes: 0

mdd061000
mdd061000

Reputation: 366

It looks like you're wanting to grab values from new if old is NULL and old if new is null. To do that you can use a case statement in your SQL. I know CASE statements are supported by MySQL, I'm not sure if they'll help you here.

SELECT (CASE WHEN old_col IS NULL THEN new_col ELSE old_col END) as val FROM table_name

This will grab new_col if old_col is NULL, otherwise it will grab old_col.

Upvotes: 1

Stephen O'Flynn
Stephen O'Flynn

Reputation: 2329

It's difficult to understand what you are looking for exactly. Does the above represent before/after, or both columns that need combining/merging.

My guess is that COALESCE might be able to help you. It takes a bunch of parameters and returns the first non NULL.

Upvotes: 2

Related Questions