CODEWITHSUNDEEP
databasestringoracle-database
rockys
rockys

Reputation: 1

String manipulation in a column in an Oracle table

One of my tables' column contains names, for example as "Obama, Barack" (with double quotes). I was wondering if we can do something to make it appear as Barack Obama in the tables. I think we can do it with declaring a variable but just could not manage to find a solution.

And yes as this table contains the multiple transactions of the same person we also end up with having multiple rows of "Obama, Barack"... a data warehouse concept (fact tables).

Upvotes: 0

Views: 155

Answers (2)

Sodved
Sodved

Reputation: 8588

From the question I assume you want to:

  • Remove the quotes
  • Remove the comma
  • Swap the names

So Regexp_replace is probably your best bet

UPDATE tablename
SET    column_name = REGEXP_REPLACE( column_name, '^"(\w+), (\w+)"$', '\2 \1' )

So regexp_replace is changing the column value as long as it matches the pattern exactly. What the parts of the expression are

  • ^" means it must start with a double quote
  • (\w+) means immediately followed by a string of 1 or more alphanumeric characters. This string is then saved as the variable \1 because its the first set of ()
  • , means immediately followed by a comma and a space
  • (\w+) means immediately followed by a string of 1 or more alphanumeric characters. This string is then saved as the variable \2 because its the second set of ()
  • "$ means immediately follwed by a double quote which is the end of the string
  • \2 \1 is the replacement string, the second saved string followed by a space followed by the first saved string

So anything which does not exactly match these conditions will not be replaced. So if you have an leading or traling spaces, or more than one space after the comma, or many other reasons the text will not be replaced.

A much more flexible (maybe too flexible) option could be:

UPDATE tablename
SET    column_name = REGEXP_REPLACE( column_name, '^\W*(\w+)\W+(\w+)\W*$', '\2 \1' )

This is similar but effectively makes the quotes and the comma optional, and deals with any other leading or trailing pubctuation or whitespace.

  • ^\W* means must start with zero or more non-alphanumberics
  • (\w+)\W+(\w+) means two alphanumberic strings separated by one or more non-alphanumerics. The two strings are saved as described above
  • \W*$ means must then end with zero or more non-alphanumberics

More info on regexp in oracle is here

Upvotes: 1

Eddie
Eddie

Reputation: 700

What @Ben has said is correct. Having two columns one for first name and one for last name is correct.

However if you wish to update the entire database as it is you could do...

  /*This will swap the order round*/
  UPDATE TableName SET NameColumn = SUBSTRING(NameColumn, 1, CHARINDEX(',',NameColumn))+SUBSTRING(NameColumn, CHARINDEX(',', NameColumn),LEN(NameColumn)-CHARINDEX('"', NameColumn,2))

  /*This will remove the quotes*/
  UPDATE TableName SET NameColumn = REPLACE(NameColumn, '"', '')

Edit:- but as I can't see your data you may have to edit it slightly. But the theory is correct. See here http://www.technoreader.com/SQL-Server-String-Functions.aspx

Upvotes: 1

Related Questions