SQL parsing in a proc

How can I parse a string in a proc?
LVL 1
HLRosenbergerAsked:
Who is Participating?
 
Éric MoreauSenior .Net ConsultantCommented:
just check @cnt before inserting:


                  IF @Cnt > 1
                        Insert Into @RtnValue (data)
0
 
Éric MoreauSenior .Net ConsultantCommented:
what kind of parsing do you want to do?
0
 
HLRosenbergerAuthor Commented:
Actually, I found a way to do it, but it needs a tweak.

here's a call to my proc:

select * from dbo.Split_PValues('1111 annual X1212121  annual Xhghghf')  

it returns a table:

1  1111
2  X1212121
3  Xhghghf

However, I do not want the "row" before the first "annual".  As output I want:

1  X1212121
2  Xhghghf


here's the proc:


BEGIN
      Declare @Cnt int
      Set @Cnt = 1
      Declare @delimiter nvarchar(6)
      Set @delimiter = 'Annual'

      While (Charindex(@delimiter,@RowData)>0)
      Begin
            Insert Into @RtnValue (data)
            
            Select Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@delimiter, @RowData)-1)))
            
            Set @RowData = Substring(@RowData,Charindex(@delimiter, @RowData) + len(@delimiter),len(@RowData))
                                                
            Set @Cnt = @Cnt + 1
      End
      
      Insert Into @RtnValue (data)
      Select Data = ltrim(rtrim(@RowData))

      Return
END
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
awking00Commented:
select * from dbo.Split_PValues(substring(yourfield,charindex('annual',yourfield),len(yourfield) - charindex('annual',yourfield)))
0
 
HLRosenbergerAuthor Commented:
perfect.  Thanks!
0
 
HLRosenbergerAuthor Commented:
ah, one last question:

What I want to split is actually a text column in a table.

So instead of this:

select * from dbo.Split_PValues('1111 annual X1212121  annual Xhghghf')  

instead, effectively I want to do this:

select * from dbo.Split_PValues (select *  from CstFldDt
where CstID = 76 and CompanyID = 15 and Caption1 = 'category')  

But syntactically this does not work.
0
 
HLRosenbergerAuthor Commented:
I mean this:

select pvalues from dbo.Split_PValues (select *  from CstFldDt
where CstID = 76 and CompanyID = 15 and Caption1 = 'category')
0
 
HLRosenbergerAuthor Commented:
sorry.  I mean this:

select * from dbo.Split_PValues (select Pvalues  from CstFldDt
where CstID = 76 and CompanyID = 15 and Caption1 = 'category')
0
 
awking00Commented:
Perhaps
Set @RowData = select Pvalues  from CstFldDt where CstID = 76 and CompanyID = 15 and Caption1 = 'category'
0
 
Éric MoreauSenior .Net ConsultantCommented:
you need a cross apply:


select  *
from CstFldDt AS C
CROSS APPLY dbo.Split_PValues(C.PValues) AS PV
where C.CstID = 76
and C.CompanyID = 15
and C.Caption1 = 'category'
0
 
HLRosenbergerAuthor Commented:
emoreau - Thanks again!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.