I am a little lost, to be honest I don't even know where to begin on this one. I need to count the lines of that returns on my script. I will attach an example. There is nothing that I can find that leads me in the right direction on this. The last line I show tallies the lines above and then puts them in B RECS. I have everything but the B RECS any help would be greatly appreciated.

"
PID=xxxxxx XXXXXXXX SID=xxxxxx XXXXXXXX START  100129 3.0.0                                                             
S0000xxxxxxDEPOSIT1234           AUAX371449635398431    1210000000006989840   1                                         
ABEMPLOYER *SERVICES            D              US                                                                       
A21 ADP BLVD                                                                                                            
A3ROSELAND, NJ 07068                                                                                                    
B RECS=000000005
"
Benjie

benjiej


Unless one of the SQL gurus knows different there isn't an SQL function to count the occurrences of a substring in a string. I'd suggest checking out the user defined functions in engine customisation and producing your own.

Roy Lambert [Team Elevate]

benjiej

> I am a little lost, to be honest I don't even know where to begin on this
one. I need to count the lines of that returns on my script. I will attach
an example. There is nothing that I can find that leads me in the right
direction on this. The last line I show tallies the lines above and then
puts them in B RECS. I have everything but the B RECS any help would be
greatly appreciated.
>
> "
> PID=xxxxxx XXXXXXXX SID=xxxxxx XXXXXXXX START  100129 3.0.0
> S0000xxxxxxDEPOSIT1234           AUAX371449635398431
1210000000006989840   1
> ABEMPLOYER *SERVICES            D              US
> A21 ADP BLVD
> A3ROSELAND, NJ 07068
> B RECS=000000005
> "

Presuming the "script" is in a memo field then you could check the result
with something like

SELECT Memofield+#13+#10+'B
RECS='+right('000000000'+cast(occurs(#13,Memofield) as char(5)),9) FROM
Table

If this is what you want then you can update the memo with

UPDATE Table SET Memofield=Memofield+#13+#10+'B
RECS='+right('000000000'+cast(occurs(#13,Memofield) as char(5)),9)

John

John


You are good. I never knew OCCURS existed.

Roy Lambert

"Roy Lambert" <roy.lambert@skynet.co.uk> wrote in message
news:867039E1-7087-461A-A75C-0AE49D3B309E@news.elevatesoft.com...
>
> You are good. I never knew OCCURS existed.
>

What my 2nd best SQL guy didnt know about Occurs ?
Johns my BF u get your own.
Rita