How to split a string in sql server 2008 using stored procedure and insert the data to table

Question

0.00/5 (No votes)

See more:

I want to split a string in this format

Quote:
"date=10/10/2000|age=13^date=01/01/2001|age=12^date=02/02/2005|age=8"

.
Actually this string is only a sample one my original string is very large . i am not getting a point that if i break this string than how many variables i have to make to capture the data aslo after splitting the string i want that to be inserted into datatable containing columns as date and age? What concept do i use?(I am getting this string from a web service) Thanks in advance..

Posted 22-Aug-13 0:40am

Rambo_Raja

Updated 22-Aug-13 0:42am

v3

Add a Solution

Comments

Maciej Los 22-Aug-13 6:51am

Do you want result like this:

Date       |    Age
10/10/2000 |  13
01/01/2001 |  12
02/02/2005 |   8

Rambo_Raja 22-Aug-13 6:56am

yes yes yes...you are a master man...exactly like this

Maciej Los 22-Aug-13 7:10am

Please, see my answer ;)

[no name] 12-Dec-14 7:37am

You can split using this example http://arbtech.co.in/search_blog.aspx?q=SQL-Query-to-Split-string-by-specific-seperator

4 solutions

Solution 1

See:

SQL Server - Split delimited string into separate columns[^]

And see also:
CP Article: SQL User Defined Function to Parse a Delimited String[^]
SO Question: Split string in SQL[^]

Good luck,
Edo

Posted 22-Aug-13 0:52am

Joezer BH

Updated 22-Aug-13 0:56am

v2

Solution 2

Create UDF and use in Stored Procedure

SQL

CREATE FUNCTION [dbo].[fnSplitString] 
( 
    @string NVARCHAR(MAX), 
    @delimiter CHAR(1) 
) 
RETURNS @output TABLE(splitdata NVARCHAR(MAX) 
) 
BEGIN 
 
    DECLARE @start INT, @end INT 
    SELECT @start = 1, @end = CHARINDEX(@delimiter, @string) 
 
    WHILE @start < LEN(@string) + 1 BEGIN 
        IF @end = 0  
            SET @end = LEN(@string) + 1
       
        INSERT INTO @output (splitdata)  
        VALUES(SUBSTRING(@string, @start, @end - @start)) 
        SET @start = @end + 1 
        SET @end = CHARINDEX(@delimiter, @string, @start)
        
    END 
 
    RETURN 
 
END

Exceute this T-sql statements to create function and use as

·

SQL

select *from dbo.fnSplitString('Querying SQL Server','')

See
UDF

Posted 22-Aug-13 0:59am

uspatel

Solution 6

SQL

/*get HoursMinuts (hhmm) from date using CONVERT Function */
SELECT RIGHT('00' + CONVERT(varchar(2),(DATEPART(hour, GETDATE()))),2) + RIGHT('00' + CONVERT(varchar(2),(DATEPART(minute, GETDATE()))),2)
/*get Hours:Minuts (hh:mm) from date using CONVERT Function */
SELECT RIGHT('00' + CONVERT(varchar(2),(DATEPART(hour, GETDATE()))),2) +':'+ RIGHT('00' + CONVERT(varchar(2),(DATEPART(minute, GETDATE()))),2)

/*get HoursMinuts (hhmm) from date CONVERT Function */
SELECT RIGHT('00' + CAST(DATEPART(hour, GETDATE()) AS varchar(2)), 2) + RIGHT('00' + CAST(DATEPART(minute, GETDATE()) AS varchar(2)), 2)
/*get Hours:Minuts (hh:mm) from date CONVERT Function */
SELECT RIGHT('00' + CAST(DATEPART(hour, GETDATE()) AS varchar(2)), 2) +':'+ RIGHT('00' + CAST(DATEPART(minute, GETDATE()) AS varchar(2)), 2)

Posted 29-Apr-15 7:35am

Member 11297580

Comments

Maciej Los 29-Apr-15 15:40pm

Why to post an answer to the question which is already solved?

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2013-08-22T01:06:00

Solution 3

I prefer to use Common Table Expressions[^].
Have a look here:

SQL

DECLARE @s VARCHAR(300)
SET @s = 'date=10/10/2000|age=13^date=01/01/2001|age=12^date=02/02/2005|age=8'

DECLARE @tmp TABLE(aDate DATETIME, aAge INT)

;WITH MyRows AS
(
    SELECT LEFT(@s, CHARINDEX('^', @s) -1) AS MyRow, RIGHT(@s, LEN(@s) - CHARINDEX('^', @s)) AS Remainder
    UNION ALL
    SELECT LEFT(Remainder, CHARINDEX('^', Remainder) -1) AS MyRow, RIGHT(Remainder, LEN(Remainder) - CHARINDEX('^', Remainder)) AS Remainder
    FROM MyRows
    WHERE CHARINDEX('^', Remainder)>0
    UNION ALL
    SELECT Remainder AS MyRow, NULL AS Remainder
    FROM MyRows
    WHERE CHARINDEX('^', Remainder)=0
)
INSERT INTO @tmp (aDate, aAge)
SELECT CONVERT(DATETIME, SUBSTRING(MyRow, 6, 10)) AS aDate, CONVERT(INT,SUBSTRING(MyRow, 21, LEN(MyRow))) AS aAge
FROM MyRows

SELECT *
FROM @tmp

Result:

2000-10-10 00:00:00.000    13
2001-01-01 00:00:00.000    12
2005-02-02 00:00:00.000     8

Posted 22-Aug-13 1:06am

Maciej Los

Comments

Rambo_Raja 22-Aug-13 7:19am

great!thanx..

Maciej Los 22-Aug-13 7:33am

You're welcome ;)

Rambo_Raja 22-Aug-13 7:21am

great!thanx..

Aso dear i am getting this data (string) from web service(asp.net). this data is not fixed. can u help me out? thanx..

Maciej Los 22-Aug-13 7:35am

What do you want to know?

Rambo_Raja 22-Aug-13 8:12am

The string which u have splited is not fixed . The string may contain data of about 50 rows or of about 10 rows. In this case u have used 3 select statement because u know about the data ( i.e string contains data for 3 rows) but what if i get the string data as a input parameter i.e not knowing the string . I have created a asp.net web service so i want to get data from it . data is not fixed.? ;) maybe u have got my point.

Maciej Los 22-Aug-13 8:25am

First, create stored procedure with one input variable, then you'll be able to pass string variable as an input parameter of SP.
HOW TO: Call a Parameterized Stored Procedure by Using ADO.NET and Visual C# .NET[^]

Rambo_Raja 22-Aug-13 7:22am

the string parameter is variable.

thatraja 22-Aug-13 8:40am

5!

Maciej Los 22-Aug-13 8:44am

Thank you ;)

George Tsiga 2-Mar-17 8:43am

Perfect You just saved my day :)

Maciej Los 2-Mar-17 8:51am

Great!
Cheers,
Maciej