Spliting up a string in a column

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 2005 Forums

Transact-SQL (2005)

Spliting up a string in a column

Author

Topic

spinning starlet
Starting Member

3 Posts

Posted - 2008-04-03 : 06:45:26

Good Morning,

I'm trying to write a scrip to compate two columns in two different tables to each other (billing_payee to Debtor_name)

The problem i have is that the info in the billing_payee column is CLIENT RE DEBTOR INITAL SURN and the infor in Debtor_name column is [i]DEBTOR TITLE NAME SURN[i]

What i need to be able to do is select all from Billing_Payee table where the Billing payee [i]DEBTOR INIAL SURN[i] is not the same as the info in Debtor_name.

I somehow need to split out the inial - first letter of debtor name and surname.

If i can't do it this way i need some other way of comaparing the two.

I am fairly new to t-SQL however have a pretty good grasp of it.

Please let me know if there is anything else i can provide to make this any easier any help is much appreciated!!

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-04-03 : 06:48:11

Please post some sample data and expected output.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

spinning starlet
Starting Member

3 Posts

Posted - 2008-04-03 : 07:58:52

Example tables

billing_payee
Nationwide re Mr B Jones
Nationwide re Miss G Green
Nationwide re Mrs t Smith

Debtor_name
Mr Benjamin Jones
Mrs Gemma Forest
Miss Tracey Smith

Expected output would be

Nationwide re Miss G Green Mrs Gemma Forrest
Nationwide re Mrs T Smith Miss Tracey Smith

So the output is
Select billing_Payee, Debtor_name from table[/] JOIN [i]table2 where (and this is the part i am stuck on)

I hope this is enough info for you to be able to help!!

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2008-04-03 : 12:16:55

There certainly has to be more in the tables than names. exec sp_help tablename for both tables. What columns are there? There should be a foreign key from payee to debtor (or the other way around) so that you can join them.

Terry

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2008-04-04 : 04:46:33

hi,

this will help

SELECT billingtype,bp.billername,dn.billername
FROM
billing_payee bp,Debtor_name dn
WHERE SUBSTRING(dn.billername,1,CHARINDEX(SPACE(1),dn.billername)) +
SUBSTRING(dn.billername,CHARINDEX(SPACE(1),dn.billername) + 1,1) +
REVERSE(SUBSTRING(REVERSE(dn.billername),1,CHARINDEX(SPACE(1),REVERSE(dn.billername)))) = bp.billername

spinning starlet
Starting Member

3 Posts

Posted - 2008-04-04 : 06:49:05

Apologies,

There is a lot more in the tables, however most of it is sensative data.

I have worked out how to join the tables.

thank you for your help much appreciated.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources