Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Parse string into columns

Posted on 2007-10-16
14
Medium Priority
?
283 Views
Last Modified: 2012-05-05
hi,

I have a function like this that retuen a sting like this "123 as one, 456 as two", is there a way to parse it into different columns when it returns?  thx
0
Comment
Question by:mcrmg
  • 5
  • 5
  • 3
  • +1
14 Comments
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 20090263
Only if you call it using EXEC(SQL)

i.e.

'SELECT ' + '23 as one, 456 as two' + ' FROM YourTable'

EXEC('SELECT 23 as one, 456 as two FROM YourTable')


This probably doesn't answer your question, but it might give you an idea of what extra information you need to provide.
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 20090587
Try this:

SELECT
      SUBSTRING(String,1,CHARINDEX(',',String)) as Col1
      SUBSTRING(String,CHARINDEX(',',String)+1,len(String)) as Col2

where "string" can be your function.
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 20093807
Why not just create another function that returns a table of the results instead of a single string?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:mcrmg
ID: 20094058
can you give me an example how to retuen a table from a funtion?  thx
0
 

Author Comment

by:mcrmg
ID: 20094384
the table that returns, I would assume that has mutiple rows instead of cols?  correct?  thx
0
 
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 400 total points
ID: 20094428
CREATE FUNCTION dbo.ReturnATable (
    @string VARCHAR(200)
    )
RETURNS @values TABLE (
    value VARCHAR(50)
    )
AS
BEGIN
DECLARE @delimiterLocation INT
SET @delimiterLocation = CHARINDEX(',', @string)
WHILE @delimiterLocation > 0
BEGIN
    INSERT INTO @values
    SELECT LTRIM(LEFT(@string, @delimiterLocation - 1))
    SET @string = SUBSTRING(@string, @delimiterLocation + 1, 500)
    SET @delimiterLocation = CHARINDEX(',', @string)
END --WHILE
INSERT INTO @values
SELECT LTRIM(@string)
RETURN
END --FUNCTION


SELECT *
FROM dbo.Returnatable ('123 as one, 456 as two')



0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 20094466
This function returns only 1 column.

Have you tried my solution yet? You can use it in your existing function if you wanted, for sure you don't need to create another one.
0
 

Author Comment

by:mcrmg
ID: 20094510
@Zberteoc:
I tried it...in my code, it actually retuen 4 values, such as
'111' as a, '222' as b, '333' as c, '444' as d
the problem is that it will not ALWAYS return 4, could be 3, 2 or just 1 value....any ideas?  thx
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 20094533
At this point I thik we need some data and code samples. I am not sure I understant what you need.

As it is my code only returns 2 values/columns:

SELECT
      SUBSTRING(String,1,CHARINDEX(',',String)) as Col1
      SUBSTRING(String,CHARINDEX(',',String)+1,len(String)) as Col2
0
 

Author Comment

by:mcrmg
ID: 20094650
okay, this is a payment system,

I need to track 4 month back of payments from the users, it will return status of that user, some users just joined two month ago, so the payment will return two times,

'OnTime' AS one, 'OnTime' AS two, 'LATE' as three, 'OnTime' as four (this is for the user that joined for at least 4 month)

once the function returns this string, I want to put it into different cols...


thx
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 20094654
>> can you give me an example how to retuen a table from a funtion?  thx <<

I thought he just wanted an *example* of how to return a table, not necessarily exactly matching this specific request.

I agree -- I don't think I fully understand the requirements enough yet to generate a full solution.  But, I would strongly urge that a function be used so that it can be used as part of a SELECT when needed.
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 20094690
<<'OnTime' AS one, 'OnTime' AS two, 'LATE' as three, 'OnTime' as four (this is for the user that joined for at least 4 month)

once the function returns this string, I want to put it into different cols...>>

Sorry, but I still don't understand what you need. As I see in your example is not ONE string but four. Please paste here some data that you retrieve and maybe the code that retrieves it.
0
 

Author Comment

by:mcrmg
ID: 20094737
I am sorry, should be like this when it returns:
'OnTime AS one, OnTime AS two, LATE' as three, OnTime' as four'




CREATE FUNCTION dbo.fn_payment (@NameID int, @FromDate datetime, @userNumber varchar(20))  
RETURNS varchar(900)
AS
BEGIN

declare @res varchar(900)
SET @res = ''
SELECT @res=@res + StatusCode + ','
FROM v_StatusHistory
WHERE       (NameID = @NameID) AND (userNumber = @userNumber)
and
payDate between dateadd(m,-4, @FromDate) and @FromDate
ORDER BY payDate DESC

RETURN @res

END


0
 
LVL 27

Accepted Solution

by:
Zberteoc earned 700 total points
ID: 20095127
Ok, the easyest way to do this is to use some dynamic. If your string is 'OnTime AS one, OnTime AS two, LATE as three, OnTime as four' then use this:

DECLARE
      @string nvarchar(2000),
      @sql nvarchar(4000)

SELECT
      -- here you can replace with @string=dbo.fn_payment(<params_here>)
      @string='OnTime AS one, OnTime AS two, LATE as three, OnTime as four'

-- ## below I am adding  sigle quotes and using a trick
-- to get rid of the last , if returned by your function
SELECT
      @sql=reverse('SELECT '+ replace(replace(@string,'OnTime','''OnTime'''),'LATE','''LATE'''))
WHILE
      (SELECT charindex(',',@sql))=1
      SELECT @sql=right(@sql,len(@sql)-1)
SELECT @sql=reverse(@sql)
-- ## end

EXEC(@sql)
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question