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.
| Author |
Topic |
|
Pinto
Aged Yak Warrior
590 Posts |
 Posted - 2010-08-18 : 05:28:36
|
| I have inherited a badly design database where the official's name is stored as A Brownwell,Jane SmithB BloggsIs there a way I can sort by surname ? |
|
|
Devart
Posting Yak Master
102 Posts |
Posted - 2010-08-18 : 05:49:24
|
| Hi,Try this:substring(<your_field_name>,patindex('% %',<your_field_name>)+1,datalength(<your_field_name>))Best regards,Devart,SQL Server Tools:dbForge Schema ComparedbForge Data ComparedbForge Query Builder |
 |
|
|
Pinto
Aged Yak Warrior
590 Posts |
Posted - 2010-08-18 : 06:27:08
|
| Thanks for that - I wanted to use it in a Crystal formula and it won't work in that...... it works in a sql expression. |
|
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2010-08-18 : 07:52:07
|
| This is a relatively common problem and I haven't seen any fool proof solutions. If your column only has first name and last name then you can always split it in two. Unless the first name is Oscar and last name is de la Hoya. Or Michael J. Fox or etc., etc.This works pretty well splittnig up the namesSELECT PARSENAME(REPLACE ('A Brownwell',' ','.'),1) ,PARSENAME(REPLACE ('A Brownwell',' ','.'),2)JimEveryday I learn something that somebody else already knew |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2010-08-18 : 08:51:50
|
I think parseName is limited to 4 parts so be warned. Example:DECLARE @foo VARCHAR(50)SET @foo = 'a b c d e'SELECT PARSENAME(REPLACE(@foo, ' ', '.'), 1)SET @foo = 'a b c d'SELECT PARSENAME(REPLACE(@foo, ' ', '.'), 1) There's no good way to do this. If the data is as you describe and there can be any number of different 'words' inside the string then you are always going to have to do some manual viewing of the data to check it.You could probably automate patterns. Say 1 character then a space then a word -- the word is probably the surnameThat might get rid of most of the data.Then inspect the rest by hand (a PITA I Know)Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
|
|
|
|
|
Extract a surname