Skip to content
Advertisement

Splitting alphumeric column in SQL

I currently have a dataset looking like this

J_J      WMT        MSF
6.7M     167.8M     1.6k
544K     700.7B     875k
888.7B   900K       98.5M

I want to split the string values for each column so I could watch something like this:

Column1   Column2
6.7         M
544         K  
888.7       B

I am currently using the following code for the 1st column:

SELECT REPLACE("J_J", '[0-9]') AS Letters,
       REPLACE("J_J", '[A-Z-z]') AS Numbers
FROM   data

which gives me the following error:

The replace function requires 3 argument(s).

How could I declare argument function?,

Thanks in advance

Answer

Most databases support left() and right() and either len() or length(). That suggests:

select left(J_J, len(J_J) - 1), right(J_J, 1)