14,297,353 members
Rate this:
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
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

Rate this:

## 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
)
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```
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:

v2
Rate this:

## 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:

## 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)```
Maciej Los 29-Apr-15 15:40pm

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