SQL Server 2008 - Split

T-SQL split string

I've used this SQL before which may work for you:-

CREATE FUNCTION dbo.splitstring ( @stringToSplit VARCHAR(MAX) )
RETURNS
 @returnList TABLE ([Name] [nvarchar] (500))
AS
BEGIN

 DECLARE @name NVARCHAR(255)
 DECLARE @pos INT

 WHILE CHARINDEX(',', @stringToSplit) > 0
 BEGIN
  SELECT @pos  = CHARINDEX(',', @stringToSplit)  
  SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1)

  INSERT INTO @returnList 
  SELECT @name

  SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos)
 END

 INSERT INTO @returnList
 SELECT @stringToSplit

 RETURN
END

and to use it:-

SELECT * FROM dbo.splitstring('91,12,65,78,56,789')

Split function by comma in SQL Server 2008

I've change the function name so it won't overlapped in what the Split() function really does.

Here is the code:

CREATE FUNCTION dbo.GetColumnValue(
@String varchar(8000),
@Delimiter char(1),
@Column int = 1
)
returns varchar(8000)
as     
begin

declare @idx int     
declare @slice varchar(8000)     

select @idx = 1     
    if len(@String)<1 or @String is null  return null

declare @ColCnt int
    set @ColCnt = 1

while (@idx != 0)
begin     
    set @idx = charindex(@Delimiter,@String)     
    if @idx!=0 begin
        if (@ColCnt = @Column) return left(@String,@idx - 1)        

        set @ColCnt = @ColCnt + 1

    end

    set @String = right(@String,len(@String) - @idx)     
    if len(@String) = 0 break
end 
return @String  
end

And here is the usage:

select dbo.GetColumnValue('Col1,Field2,VAlue3', ',', 3)

SQL Server 2008 split string fails due to ampersand

Edit your function and replace all & as &
This will remove the error. This happens because XML cannot parse & as it's an inbuilt tag.

split string in sql and add each seprareted value into each column of table

There are many solution available for string splitting requirements, you can use one of string splinting function from here fnSplitString

I just demonstrating how to use this function in your case .

Try to convert all -,; with :

declare @data varchar(max)=null
set @data='nt:865067021846160;2;8.5.05;1,1,20161010102239.000,18.580423,73.815948,549.700,0.28,33.6,11;101;100;0;0;0;FF;146;25}'
SET @data=REPLACE(REPLACE(@data,'-',':'),';',':')

Now Call this fnSplitString as below, and this will gives single column table with all string fragments.

INSERT INTO #TableName(Col1) SELECT * FROM dbo.fnSplitString(@data,':')

This will gives OUTPUT like this

nt
865067021846160
2
8.5.05
1,1,20161010102239.000,18.580423,73.815948,549.700,0.28,33.6,11
101
100
0
0
0
FF
146
25}

Mimic STRING_SPLIT without custom function in SQL Server 2008

DECLARE @Result Table(Value varchar(50))
DECLARE @x XML
SELECT @X = CAST('<A>' + REPLACE(@StringList, '|', '</A><A>') + '</A>' AS XML)

INSERT INTO @Result
SELECT t.value('.', 'varchar(50)') as inVal
FROM @X.nodes('/A') AS x(t)

This will create a table with one column (Value). Each split value from your pipe-delimited string will create a new record in this table. Then you can join to it however you'd like. Please let me know if this is unclear or if it doesn't work on SQL 2008.

You can increase the size of the varchar, if needed - and you can modify the query to split on different values (comma-delimited, etc.).

Split and inner join return string in row SQL Server 2008

This here will do the trick for you.

You need to create a function which can split out your rows so you can join them afterwards and use som XML to concat your rows back.

You need to create a function which can split your rows or unconcat

    CREATE FUNCTION [dbo].[dba_parseString_udf] (
      @stringToParse VARCHAR(8000)  
    , @delimiter     CHAR(1)
)
RETURNS @parsedString TABLE (stringValue VARCHAR(128)) AS
BEGIN

/* Declare variables */
DECLARE @trimmedString  VARCHAR(8000);

/* We need to trim our string input in case the user entered extra spaces */
SET @trimmedString = LTRIM(RTRIM(@stringToParse));

/* Let's create a recursive CTE to break down our string for us */
WITH parseCTE (StartPos, EndPos)
AS
(
    SELECT 1 AS StartPos
        , CHARINDEX(@delimiter, @trimmedString + @delimiter) AS EndPos
    UNION ALL
    SELECT EndPos + 1 AS StartPos
        , CHARINDEX(@delimiter, @trimmedString + @delimiter , EndPos + 1) AS EndPos
    FROM parseCTE
    WHERE CHARINDEX(@delimiter, @trimmedString + @delimiter, EndPos + 1) <> 0
)

/* Let's take the results and stick it in a table */  
INSERT INTO @parsedString
SELECT SUBSTRING(@trimmedString, StartPos, EndPos - StartPos)
FROM parseCTE
WHERE LEN(LTRIM(RTRIM(SUBSTRING(@trimmedString, StartPos, EndPos - StartPos)))) > 0
OPTION (MaxRecursion 8000);

RETURN;   
END

Inner table here is your Table2 and split table is your table1

SQL Code to run

    with result as (
SELECT  [id]
      ,[name]
      ,i.stringValue
from [LegOgSpass].[dbo].[inner] as  a
  cross apply [dbo].[dba_parseString_udf](a.Name,';') i
  )

  ,partresult as (
  select a.id,a.name,b.name as RowstoConcat from result a
  left join LegOgSpass.dbo.split b on a.stringValue = b.id
  )
    SELECT id,Name, Pets = STUFF((SELECT N', ' + RowstoConcat 
  FROM partresult AS p2
   WHERE p2.name = p.name 
   ORDER BY RowstoConcat
   FOR XML PATH(N'')), 1, 2, N'')
FROM partresult AS p
GROUP BY id,Name
ORDER BY id,Name

Result

---

Related Topics