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 |
|
PalaDolphin
Starting Member
4 Posts |
 Posted - 2012-08-23 : 11:25:54
|
| I'm using MySQL and have imported a database of memorable quotations. One of the fields is Author in the format of '[first] [:space:] last' name. I'm experienced in relational expressions and thought of using that to parse that field and create a field that I can use to order by last name first. However, I don't know how to put that into a SQL statement. The manual gives me examples that return 0 or 1. I want output more like sed does.For example, if name is 'Robert Wilson',and the REGEXP is '/^\(.*\) \(.*\)$/$2, $1/The output would be: 'Wilson, Robert'I guess what I'm looking for is a regular expression replace function.Otherwise, I'll have to use PHP and build a temp tables, then import it to an UPDATE statement.Sincerely,-=- PalaDolphin |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2012-08-23 : 11:35:13
|
| You might want to try a MySQL forum, this is a SQL Server forum. In T-SQL, there is a REPLACE() function, but not sure about MySQL and somehow doubt it.Try http://forums.mysql.com/ |
 |
|
|
PalaDolphin
Starting Member
4 Posts |
Posted - 2012-08-23 : 12:50:18
|
| Is there a way to do it in SQL Server?-=- PalaDolphin |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2012-08-23 : 13:27:43
|
No regular expressions in MS SQL Server.But for your example data you can use functions like left(), right(), substring(), charindex(), stuff() ...
Too old to Rock'n'Roll too young to die. |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-08-23 : 15:05:39
|
| If you do need to use regular expressions i.e., your patterns are more complex than the ones in your example, you can do it using CLR procedures. See Fill Factor's article here: http://www.simple-talk.com/sql/t-sql-programming/clr-assembly-regex-functions-for-sql-server-by-example/ |
|
|
|
|
|
|
|
|
REGEXP to parse first last name