1

I want to match two tables based on a specific column which is a card number, there are spaces between characters in some of card numbers in the second table like below:

'NES 123_4_5'
'MTS 678_9_0'

What i want is to remove the space between the characters and numbers and change it to following form:

'NES123_4_5'
'MTS678_9_0'

What would be the SQL query of function to do this?

5

simplest way - use REPLACE function

select REPLACE('NES 123_4_5', ' ', '')
Result - NES123_4_5

full list of string functions - https://www.postgresql.org/docs/9.6/static/functions-string.html

2
  • 1
    If we want to do this for an entire column we can update the column: update TableName set ColumnName = REPLACE (ColumnName,' ',''); Oct 30 '16 at 10:21
  • Yes of course if You need update column - You update using this function, but if for any reason You must leave column in original state You can use functions in SELECT
    – a_vlad
    Oct 30 '16 at 10:33
1

If you want to replace all spaces, use replace()

replace('NES 123_4_5', ' ', '')

If you only want to replace the space after the first three characters, you can use regexp_replace()

regexp_replace('NES 123_4_5', '(^[A-Z]{3})( )', '\1')

The regular expression will look for there (uppercase) characters at the beginning followed by a single space and will remove that space

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.