I have a table which contains postcodes split into two sections (prefix & suffix). The table also contains part postcodes (e.g. just prefix, suffix is NULL)
I want to write a function that will return either the record which matches will both parts or if nothing found returns a match against the prefix only.
ID Prefix Suffix
1 SS1 2EF
2 SS1 6TF
3 SS1 NULL
If I search for Prefix = 'SS1', Suffix = '2EF' the query returns 1
If I search for Prefix = 'SS1', Suffix = '7GS' the query returns 3