how to separate string into different columns?

Question

I've a table with entries like this.

    MachineName
-----------------------

    Ab bb zecos
    a zeng
    zeng
    empty

4 rows in a table.

How can i seperate those 4 rows to get output like.

       M1       M2       M3
-----------------------------------
       Ab       bb      zecos
       a        zeng     NULL
       zeng     NULL     NULL
       NULL     NULL     NULL

Answer 1

Instead of using split function there is a function called ParseName which returns the specified part of the object which spilts the string delimated by . Please go through the ParseName link which helped me in writing this query

Declare @Sample Table
(MachineName varchar(max))

Insert into @Sample
values 
('Ab bb zecos'),('a Zeng')


  SELECT 
  Reverse(ParseName(Replace(Reverse(MachineName), ' ', '.'), 1)) As [M1]
 , Reverse(ParseName(Replace(Reverse(MachineName), ' ', '.'), 2)) As [M2]
 , Reverse(ParseName(Replace(Reverse(MachineName), ' ', '.'), 3)) As [M3]

  FROM  (Select MachineName from @Sample
  ) As [x] 

Answer 2

Try this one:

    CREATE FUNCTION [dbo].[SplitIndex](@Delimiter varchar(20) = ' ', @Search varchar(max), @index int)
    RETURNS varchar(max)
    AS
    BEGIN
          DECLARE @ix int,
                      @pos int,
                    @rt varchar(max)

          DECLARE @tb TABLE (Val varchar(max), id int identity(1,1))

          SET @ix = 1
          SET @pos = 1


          WHILE @ix <= LEN(@search) + 1 BEGIN

                SET @ix = CHARINDEX(@Delimiter, @Search, @ix)

                IF @ix = 0
                      SET @ix = LEN(@Search)
                ELSE
                      SET @ix = @ix - 1

                INSERT INTO @tb
                SELECT SUBSTRING(@Search, @pos, @ix - @pos + 1)

                SET @ix = @ix + 2
                SET @pos = @ix
          END

          SELECT @Rt = Val FROM @Tb WHERE id = @index
          RETURN @Rt     
    END

Use like so:

SELECT dbo.SplitIndex(' ', 'hello World', 1)

Combine that with Dems answer and you should be good to go

(Note it will return NULL if the specified index does not exist)

e.g.

SELECT dbo.SplitIndex(' ', 'Hello World', 3)  

would return NULL because index 3 does not exist

Not sure what performance is like though, I just modified a table valued split function that I'd done so it might be worth rewriting to be more like the MySql version

Answer 3

Use Parsename() function

with cte as(
    select 'Aria Karimi' as FullName
    Union
    select 'Joe Karimi' as FullName
    Union
    select 'Bab Karimi' as FullName
)

SELECT PARSENAME(REPLACE(FullName,' ','.'),2) as Name, PARSENAME(REPLACE(FullName,' ','.'),1) as Family from cte

Result

Name    Family
-----   ------
Aria    Karimi
Bab     Karimi
Joe     Karimi

Answer 4

DECLARE @Tmp TABLE (empid INT,joined nchar(10))

INSERT @Tmp SELECT 1,'1990, 1111' 
INSERT @Tmp SELECT 2,'2000, 2222' 

INSERT @Tmp SELECT 3,'1993, 3333' 

INSERT @Tmp SELECT 4,'1899, 4444' 
INSERT @Tmp SELECT 5,'1999, 5555' 

INSERT @Tmp SELECT 6,'2001, 6666 ' 


--Using PARSENAME 

SELECT empid, joined,
       PARSENAME(REPLACE(joined,',','.'),2) join1, 
       PARSENAME(REPLACE(joined,',','.'),1) join2 
FROM @Tmp

Answer 5

If you are programming in C++, please do:

#include <cstring>
#include <iomanip>

using namespace std;
int main () {
string machine[12];
for (int i = 0; i < 12; i++) {
    if (machine[i] == "")
        machine[i] = "NULL";
}

for (int i = 0; i < 3; i++) {
    cout << setw(10) << machine[i] << setw(10) << machine[i+1] << setw(10) << machine[i+2] << endl;
}
return 1;
}