First of all, congratulations on earning your MVP status. Reading the news prompted me to look this article up to help me with an address validation task I have been asked to do.
I have used the kit and have managed to solve my problem, but, I am having to use a cursor
as the xp_regex_search returns a table with the found value. Any chance we can nag Steve into fixing the link to the source code above so I can muck about with it and make it do what I want?
For those that are interested, here's more on what I'm trying to do. (Usual Disclaimer: Schema and App NOT my design
We have an address table that contains among other columns PK, CustCode, AddressLine1, AddressLine2, AddressLine3, AddressLine4, AddressLine5, AddressLine6, Postcode.
The app that uses this table is dreadful, and over time postcodes have ended up in any of the address fields. So addressLine5 might contain "MANCHESTER M13 OJR" for example.
What I currently have is the following psuedo(ish) code
Read all records with NULL postcode into a temp table consisting of above columns.
Read first record from the temp table into Cursor
CREATE TABLE #TestSearch ( matchnum int , groupnum int , value varchar ( 128 ) )
select @addline = @add1 --Sets string to be tested
EXEC master.dbo.xp_regex_match @regExStr , @addLine, 'I+' , @Ans Output --Where @RegEx is a Postcode match
if @ans = 'Y'
INSERT INTO #TestSearch ( matchnum , groupnum , value )
EXEC master.dbo.xp_regex_search @regExStr , @addline, 'I+'
if Exists(select 1 from #testSearch)
select @val = value from #TestSearch
Set postcode = @val where id = @id
Do the same for addressLine2, 3 etc
Get the next record and go back to the top
It would be much simpler for me, and probably much quicker to say
Set postcode = master.dbo.fn_regex_match_and_return_matched_value(addressLine1, @regEx)
Where Postcode is NULL
And then do AddressLine2, etc. Or build a coalesce statement. Anyway, I'm sure you get the idea.
Any other obvious solutions I have missed, please feel free to correct me
Once again, well done on the MVP thing.
http://glossopian.co.uk/"I don't know what I don't know."