Idea for fast substring search?

[#1]

Hi all,

I got a couple of customer records that need to bechecked for duplicate house numbers in the address / street field. Due to a frontend error, several entries have been created that have duplicate house numbers like this:

'Sesame Street 312 312' or 'ArlingtonRoad 7 7' or 'Queens Boulevard 22 22'

My task is to identify those entries with a duplicate street number like the ones above. Tried several combinations with substr, stuff, charindex but did not find a solution...

Thanks! Markus

[VolkerBarth]

This might not be the easiest way, and it requires v11 or above as it does make use of regular expressions, particularly of REGEX_SUBSTR.

I'm assuming the house numbers consist of digits only and follow any other non-digit parts, otherwise you will have to adapt the pattern.

The REGEXP pattern "\\s[0-9]+" looks for a blank followed by one or more digits, and the "positive lookahead zero-width assertion" expression "(?=\\s[0-9]+)" makes sure there's one more pattern following the current position.

The first WHERE condition finds all entries with at least 2 trailing blocks of digits (whether identical or not) and is intended to speed up the query (though I don't know if it really does), the second condition compares both blocks. You might omit the fist condition.

select *,
   regexp_substr(Streetname, '\\s[0-9]+$') as LastNumberBlock,
   regexp_substr(Streetname, '\\s[0-9]+(?=\\s[0-9]+$)') as SecondLastNumberBlock
from MyTable
where Streetname regexp '.*(\\s[0-9]+){2}' 
   and regexp_substr(Streetname, '\\s[0-9]+$')
     = regexp_substr(Streetname, '\\s[0-9]+(?=\\s[0-9]+$)')


That's a starting point, I'm sure a regex expert can certainly do better...

[Former Member]

Hi,

many thanks for taking the time to look into my problem. Unfortunately we´re using Sybase IQ (Sybase IQ/15.4.0.3027) and regular expressions or regexp functions are not supported - at leats that´s what I was told by our DBAs. Sorry, should have given that info in the first place...

Markus

[reimer_pods]

This forum concentrates on SQL Anywhere. You might have move luck with IQ specific question here: SAP IQ SCN

[Vlad]

Solve the task in the dumb and direct way - create the application that reads the entire table and checks every record.

By the way, an idea came to my mind. Can you split strings by the space character, and compare last two tokens. If they are equal - mark the row.

Does SAP IQ have sa_split_list or something similar?

Or simply find all row with more than 2 spaces in that column. If the list is not big enough, give it to your DBAs.