Sql code to get multiple columns out of single column

Question

I have a column like "AP.1.12345.ABCD.20150523_0523.20150524_0223".

Can i divide the column into multiple columns on basis of " . " ?

Say for above example column, the output will be like

AP 1 12345 ABCD 20150523_0523 20150524_0223.

Thus single column will be converted into 6 columns.

Please tag your question with the database you are using, so we don't have to guess that it is SQL Server. — Gordon Linoff, Jun 06 '15 at 18:51
The solution is here http://stackoverflow.com/questions/697519/split-function-equivalent-in-t-sql — PhillipH, Jun 06 '15 at 18:54
I think this a job for business logic. Retrieve value of column and split it in six different values in your code of your application (java, .net, php etc.). Database is for saving data not for operating with data — Fabio, Jun 06 '15 at 19:35
@Fabio - Many types of SQL (including T-SQL) provide plenty of suitable string functions, and ETL tools such as SSIS (which is tagged) also provide tools for carrying out this sort of string manipulation. Sure, you *could* do this in application code, but we don't even know that an application exists in this case. — Jo Douglass, Jun 07 '15 at 00:22
@Jo Douglass - I wanted it in MS SQL SERVER 2008 or higher.. Anyways I got the solution.. — Manoj Soundararajan, Jun 08 '15 at 11:02

score 0 · Answer 1 · answered Jun 06 '15 at 18:55

Search around on the web for a split() function. This isn't hard. Google "SQL Server split" and you'll have lots of choices. You actually want one that give you a second return value, the position of the substring in the larger string.

Then:

select t.*, newcols.*
from table t cross apply
     (select max(case when pos = 1 then val end) as col1,
             max(case when pos = 2 then val end) as col2,
             max(case when pos = 3 then val end) as col3,
             max(case when pos = 4 then val end) as col4,
             max(case when pos = 5 then val end) as col5,
             max(case when pos = 6 then val end) as col6             
      from dbo.split(t.col, '.') as s(val, pos)
      group by t.col
     ) newcols;

score 0 · Answer 2 · answered Jun 06 '15 at 19:54

You can also write a function that will do something like this:

DECLARE @Col    VARCHAR(64),
        @DynSQL VARCHAR(MAX)

SET @Col = 'AP.1.12345.ABCD.20150523_0523.20150524_0223'
SET @DynSQL = 'SELECT '''+REPLACE(@Col,'.',''',''')+''''

exec(@DynSQL)

This can handle any number of delimiters/columns.

score 0 · Accepted Answer · answered Jun 06 '15 at 20:50

Thanks all for your suggestions and solutions. I got the solution. After little experimenting, Assuming that the column will be as mentioned, I used substring and charindex to get the desired result. Though the query looks a bit big, still it worked out.

I would rather wish it to be a simple query than using a function.

Need to check on performance of the query though.

Sql code to get multiple columns out of single column

3 Answers3