2

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?

Improve this question

2 Answers 2

Reset to default
8

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

Improve this answer
2
  • 1
    If we want to do this for an entire column we can update the column: update TableName set ColumnName = REPLACE (ColumnName,' ','');
    – Sayad Xiarkakh
    Commented Oct 30, 2016 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
    Commented Oct 30, 2016 at 10:33
2

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

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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