Solved

SQL parsing in a proc

Posted on 2012-04-06
11
304 Views
Last Modified: 2012-06-21
How can I parse a string in a proc?
0
Comment
Question by:HLRosenberger
  • 6
  • 3
  • 2
11 Comments
 
LVL 69

Expert Comment

by:Éric Moreau
Comment Utility
what kind of parsing do you want to do?
0
 
LVL 1

Author Comment

by:HLRosenberger
Comment Utility
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
 
LVL 69

Accepted Solution

by:
Éric Moreau earned 500 total points
Comment Utility
just check @cnt before inserting:


                  IF @Cnt > 1
                        Insert Into @RtnValue (data)
0
 
LVL 31

Expert Comment

by:awking00
Comment Utility
select * from dbo.Split_PValues(substring(yourfield,charindex('annual',yourfield),len(yourfield) - charindex('annual',yourfield)))
0
 
LVL 1

Author Closing Comment

by:HLRosenberger
Comment Utility
perfect.  Thanks!
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 1

Author Comment

by:HLRosenberger
Comment Utility
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
 
LVL 1

Author Comment

by:HLRosenberger
Comment Utility
I mean this:

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

Author Comment

by:HLRosenberger
Comment Utility
sorry.  I mean this:

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

Expert Comment

by:awking00
Comment Utility
Perhaps
Set @RowData = select Pvalues  from CstFldDt where CstID = 76 and CompanyID = 15 and Caption1 = 'category'
0
 
LVL 69

Expert Comment

by:Éric Moreau
Comment Utility
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
 
LVL 1

Author Comment

by:HLRosenberger
Comment Utility
emoreau - Thanks again!
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now