Counting the number of instances of a character in a field

[collierd]

Hello

Is there a quick and easy command that returns the number of times a specific character appears

i.e. select unknowncommand('*','1*2*3')

returns 2 for 2 *

I want to compare a number of fields which use * as a delimiter and make sure they all have the same number of delimited entries

i.e. OrdNo Quantity Value
1*2*3 20*30*40 1*50*100 ok
1*2 30*50 350 not ok


Thanks

Damian

 

[ClaireHCM]

This select all the rows that are not matching the criteria

select * from a where
len(ordno)-len(replace(ordno,'*',''))<>len(quantity)-len(replace(quantity,'*',''))
or len(ordno)-len(replace(ordno,'*',''))<>len(value)-len(replace(value,'*',''))
or len(quantity)-len(replace(quantity,'*',''))<>len(value)-len(replace(value,'*',''))

 

[ClaireHCM]

If your data is like this

'1*2*3 '
or
' 1*2*3 '
or
' 1*2*3'

modify all the ordno to rteim(ltrim(ordno))
modify all the quantity to rteim(ltrim(quantity))
modify all the valueto rteim(ltrim(value))