Comparing other table data - 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 2005 Forums

Transact-SQL (2005)

Comparing other table data

Author

Topic

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2007-11-05 : 04:41:27

Hi

I have two tables, one table contains details on customers, and the other table contains a list of titles ie, Mr, Father, Officer, etc. I am having instances where in the first table the forenames field is containing a title in it eg. 'Dr Andrew' which is incorrect, the value should just be 'Andrew'. Is it possible to do a search in the forenames field that will check if any data contained in the 2nd table containing the titles is present in the customer table's forename field?

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-05 : 04:57:28

[code]DECLARE @Table1 TABLE (ForeName VARCHAR(10))
INSERT @Table1
SELECT 'Dr Andrew' UNION ALL
SELECT 'Col War' UNION ALL
SELECT 'Rev Smith'

DECLARE @Table2 TABLE (Title VARCHAR(10))
INSERT @Table2
SELECT 'Dr' UNION ALL
SELECT 'Jr.'

UPDATE t1
SET t1.ForeName = LTRIM(SUBSTRING(t1.ForeName, LEN(t2.Title) + 1, 8000))
FROM @Table1 AS t1
INNER JOIN @Table2 AS t2 ON t1.ForeName LIKE t2.Title + '%'

SELECT * FROM @Table1[/code]

E 12°55'05.25"
N 56°04'39.16"

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2007-11-05 : 05:20:09

Thanks for that, its exactly what I needed!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources