How to wirte this query

varshavmane

Hello All, I am using 2 databases. In one database I have table which has a column named PNumber and this column has data something like this : 7,189,413 7,196,205 7,202,331 7,217,254 In another database same column name is there but data is something like this: 7189413 7196205 7202331 7217254 I need to join these two column but I am not able to get the required data. Please help me... Thanks in advance....

modified on Monday, August 11, 2008 7:18 AM

Ashfield · modified on Monday, August 11, 2008 7:18 AM

I am assuming that the data type is not a numeric one, otherwise your join would work, so try

where replace(Pnumber,',','') = Pnumber

This will strip the commas out.

Bob Ashfield Consultants Ltd

varshavmane

Thanks for the reply, but its not working. I want Expiry Date data but its not showing date. All the values in that column is Null but data is there in database...

Ashfield

Ok, post the table definitions and your query and I'll try to help further.

Bob Ashfield Consultants Ltd

varshavmane

hey thanks a lot for the reply and help. Its working fine. I just used replace where I was doing join. Something like this:

SELECT CONVERT(nvarchar(12), b.pexpiry_date, 101) as Expiry_Date FROM Table_A a
left join Table_B b with(NoLock)
on replace(a.pno,',','') = b.pnumber

Thanks again... :)

Ashfield

Good, thats what I meant :)

Bob Ashfield Consultants Ltd

Blue_Boy

DECLARE @value AS VARCHAR(255) SET @value='78,567,9' SELECT REPLACE(@value,',','') I see you have already soultion but I hope you don't mind for my answer. Regards.

I Love T-SQL "Don't torture yourself,let the life to do it for you."