Click here to Skip to main content
14,297,353 members
Rate this:
Please Sign up or sign in to vote.
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
Updated 22-Aug-13 0:42am
v3
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 ;)
Rate this:
Please Sign up or sign in to vote.

Solution 3

I prefer to use Common Table Expressions[^].
Have a look here:
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
   
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
Rate this:
Please Sign up or sign in to vote.

Solution 1

   
v2
Rate this:
Please Sign up or sign in to vote.

Solution 2

Create UDF and use in Stored Procedure
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

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



See
UDF
   
Rate this:
Please Sign up or sign in to vote.

Solution 6

/*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)
   
Comments
Maciej Los 29-Apr-15 15:40pm
   
Why to post an answer to the question which is already solved?

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




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100