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
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]
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
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
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
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;
}
