Sorry Steve, one last question. I've been reading up on Full Index, the only thing I can find via Google is Full Text Index, after reading up on it I...

That is great, many thanks Steve you've been a tremendous help

Since starting this thread I have actually changed the nulls into spaces, I'm intrigued by your suggestion "But looking back at earlier threads - you could create a new table...

Basically I've a table which I'm filling in with UK PostCodes, I've now realised I should have made the table unique to stop duplicates. The problem I faced was where...

That's great Steve, thanks for getting back to me. It's returned 2 integer fields, I know the first one is the row number. I still can't get my head around...

Thanks Steve, that did work. What exactly does this show?

I've run the sql snippet and I'm getting the following error

Msg 4112, Level 15, State 1, Line 4

The ranking function "ROW_NUMBER" must have an ORDER BY clause.

I get to columns of integers, I know the first column is ID numbers, not sure what the other integers are, can you tell me what that statement does please?

I'd like to see both senarios to see what's best

Here is my script for creating the table

if object_id('PostCodesAndAddressesBt','U')is null

CREATE TABLE PostCodesAndAddressesBt

(ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,

...

Yes - you can do a select into, and do a case when login per field - as its a one off pain at first but then its done

how...

I've tried Distinct as requested and there's only about 200 duplicates

If I make a new table with the same structure, is there a way I could copy table1 into the new table using select into but substituting NULL with 'N/A'

That's great Bhaskar, I've been looking at this on and off for days not realising how simple the solution was. Thank you so much for suppling the solution so quickly

Sorry everyone that I haven't got back to you, I've been on holiday. I will be trying your solutions when my work settles down and get back to you all....