CODEWITHSUNDEEP
sql-server
agloria
agloria

Reputation: 13

What would be the best way to separate items that are in the same column within SQL?

For example I have the below column. The column consists of two types of codes. The codes that include a letter are called HCPC codes and the numeric codes are CPT codes. I need to separate the two types but having trouble finding a way to do so. Here is the original column:

Procedure Code
G2024
99201
G0348
99204
59610
4665F

I would essentially need the output to be the following:

CPT HCPC
99201 G2024
99204 G0348
59610 4665F

Thanks in advance

Upvotes: 0

Views: 137

Answers (2)

Eric McKeeth
Eric McKeeth

Reputation: 357

Since you don't have the same number of codes of each type, the below should work if you only care about that one column:

SELECT
  c1.ProcedureCode CPT, c2.ProcedureCode HCPC
FROM
  (
    SELECT
          mt.ProcedureCode, ROW_NUMBER() OVER (ORDER BY(SELECT NULL)) rowNo
    FROM  dbo.myTable AS mt
    WHERE mt.ProcedureCode NOT LIKE '%[A-Za-z]%'
  )   c1
  FULL OUTER JOIN
  (
  SELECT
        mt.ProcedureCode, ROW_NUMBER() OVER (ORDER BY(SELECT NULL)) rowNo
  FROM  dbo.myTable AS mt
  WHERE mt.ProcedureCode LIKE '%[A-Za-z]%'
  ) c2 ON c2.rowNo=c1.rowNo;

If you have other columns in the table that you need to preserve, something more like this:

SELECT <other columns>, 
    CASE WHEN ProcedureCode LIKE '%[A-Za-z]%' THEN ProcedureCode END HCPC, 
    CASE WHEN ProcedureCode NOT LIKE '%[A-Za-z]%' THEN ProcedureCode END CPT
FROM dbo.myTable

which will preserve other columns, with every row either having a value for HCPC, or a value for CPT, but never both.

Upvotes: 2

Cetin Basoz
Cetin Basoz

Reputation: 23837

Since you said they don't necessarily match with each other and assuming there is even number of rows:

SELECT
  c1.ProcedureCode CPT, c2.ProcedureCode HCPC
FROM
  (
    SELECT
          mt.ProcedureCode, ROW_NUMBER() OVER (ORDER BY(SELECT NULL)) rowNo
    FROM  dbo.myTable AS mt
    WHERE mt.ProcedureCode NOT LIKE '%[A-Za-z]%'
  )   c1
  FULL JOIN
    (
      SELECT
            mt.ProcedureCode, ROW_NUMBER() OVER (ORDER BY(SELECT NULL)) rowNo
      FROM  dbo.myTable AS mt
      WHERE mt.ProcedureCode LIKE '%[A-Za-z]%'
    ) c2 ON c2.rowNo=c1.rowNo;

And here is DBFiddle demo.

Upvotes: 1

Related Questions