Return firstname and lastname - 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

Transact-SQL (2000)

Return firstname and lastname

Author

Topic

ygeorge
Yak Posting Veteran

68 Posts

Posted - 2004-07-16 : 12:24:11

I have a table with a name column. It contains many different format of data, for example -

John Smith
John J. Smith
Mr. John Smith

I'm asked to return rows with only one space in the string, like 'John Smith', with one query. Can anybody help me with it?

Thanks,
George

ygeorge
Yak Posting Veteran

68 Posts

Posted - 2004-07-16 : 12:49:39

Just figured out -

select name from table_name
where name like '% %' and name not like '% % %'

X002548
Not Just a Number

15586 Posts

Posted - 2004-07-16 : 13:02:49

How's about:



USE Northwind
GO

CREATE TABLE myTable99(Col1 varchar(20))
GO

INSERT INTO myTable99(Col1)
SELECT 'John Smith' UNION ALL
SELECT 'John J. Smith' UNION ALL
SELECT 'Mr. John Smith'
GO

SELECT * 
  FROM myTable99
WHERE LEN(Col1) - LEN(REPLACE(Col1,' ','')) = 1
GO

DROP TABLE myTable99
GO

Brett

8-)

ygeorge
Yak Posting Veteran

68 Posts

Posted - 2004-07-16 : 14:53:30

Perfect! Thanks Brett.

George

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources