Hi There,

Thanks for you taking your time to reply.

Now I have what I need.

Best Regards

Joe joe

Hi Lenny,

This is very elegant, and exactly what I needed. I had something like cursor & loops in my mind, but this is much more handy.

Thanks alot & have a...

Hi Guys,

Thanks for all the advices. No panic, I'm not a dba for a sql server in production, and I'm not using the addresses for contacting purposes. With the...

David,

Thanks for posting the scripts that I needed. I understand most of it when I see it, but I doubt I ever have the skills to write anything like...

Hi Jeff,

I looked into the ZP4, and truely looks very handy. But I don't have any adresses in USA, only in Europe.

But thanks for the tip 🙂

Thanks alot 🙂 - just what I needed

Regards joejoe

Thanks for your advice JohnG, you saved my day !

I now realize that "The only caveat is that expressions used within the CASE statement must be of compatible data types" - which means that I can't use CASE to update a...

Thanks Jon,

I have run the script and SQL Server comes up with the following message

Msg 102, Level 15, State 1, Line 27

Incorrect syntax near '’'.

Line 27 is =...

Thanks Matsumura it works perfect! Just what I needed. Now I have a unique ID which can be order according to areas.

Joejoe

Thanks Mark,

It works !

But unfortunately I need the two last digits in the unique_id to swith to/begin with 01 after every new AreaCode. 

Your SQL gives the last digits up to 99, instead of starting with 01 with a...

Opps, I have discovered that five values was Null due to a coversion error because my source for the table was excel, and excel coverted the values Dec01, Dec02, Dec03,...

Everthing works!

I just had to cahnge the

DECLARE @t TABLE

(

    Code varchar(5) NOT NULL PRIMARY KEY

To

DECLARE @t TABLE

(

    Code varchar(5) NULL 

Joe

Hi there, The following scripts....

DECLARE @t TABLE

(

    Code varchar(5) NOT NULL PRIMARY KEY

    ,Country nvarchar(255) NULL

 ,[Level 1] nvarchar(255) NULL

 ,[Level 2] nvarchar(255) NULL

 ,[Level 3] nvarchar(255) NULL

)

INSERT INTO @t ...

Someting like this?

UPDATE A.Mytable

SET Prov = A.Mytable.Prov

FROM  A.MyTable

    JOIN  B.MyTable

        ON LEFT(A.MyTable.Code, 4) = B.MyTable.Code

            AND A.MyTable.Code <> B.MyTable.Code

Joe