How to match fields - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

How to match fields

Author

Topic

dnagahawatte
Starting Member

24 Posts

Posted - 2004-06-25 : 06:56:24

Hi Guys;
I have two tables say tableA and tableB.(Imported from two Excel sheets)
TableA has a column with jobNumber and another column with OrderNumber.
JobNumber is a sixdigit Number (type float)
OrderNumber is a Nine(9) or Seven(7) digit number (NVarchar)
(Nine digitnumbers are starting with RA->ex:RA2045739
and Seven Digitnumbers are starting with RG->ex:RG34384)

TableB has a column with OrderNumbers.(type Float)
These are the same ordernumbers as in tableA but in Nine digit numbers; RA replaced by 2 and in Seven Digit numbers; RG replaced by 100.
So then all of them are 10 gidit numbers.(Ex:22045739 or10034384)

Now what i want is a query to match OrderNumbers and get the relevent
jobnumber for tableB.ordernumber.
I thought i should replace letters in table1.ordernumbers by 2 or 100 accordingly and then match tableA.ordernumber to tableB.ordernumber.

How do i get about doing this??
Or is there any other approch to this
Can somebody please help.
if i have to do this manually then its gonna be days as there are about 3000 records and it should happen every month...!!!

nr
SQLTeam MVY

12543 Posts

Posted - 2004-06-25 : 07:08:50

from tableA a
join tableB b
on b.Ordernumber = case when left a.ordernumber = 'RA' then '2' else '1' end + right('000000000' + right(a.ordernumber, len(a.ordernuber)-2),9)

or you could
on b.Ordernumber = replace(replace(a.ordernumber,'RA','2'),'RG,'100')

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

dnagahawatte
Starting Member

24 Posts

Posted - 2004-06-25 : 09:12:26

Thanks
It works fine.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources