Removing spaces in concatenated string column in a view

Question

I have run the following script to create a view which provides the results I want:

SELECT     ACTNUMBR_1, ACTNUMBR_2, ACTNUMBR_3, ACTNUMBR_1 + '-' + ACTNUMBR_2 AS Match_Account, ACTNUMBR_1 + '-' + ACTNUMBR_2 + '-' + ACTNUMBR_3 AS Full_Account_Number
FROM dbo.GL00100

but my data has gaps in the Match_Account and Full_Account_Number columns. See the output below:

enter image description here

I would like my account numbers to appear like - A100-000 OR A100-000-000.

What is the easiest way to remove all spaces on this view to achieve this?

Thanks,

Also, duplicate of [Removes all white spaces of a string in SQL Server](http://stackoverflow.com/questions/10432086/removes-all-white-spaces-of-a-string-in-sql-server) — LittleBobbyTables - Au Revoir, Nov 16 '12 at 16:13

score 3 · Accepted Answer · answered Nov 16 '12 at 16:10

3

Just use REPLACE to remove your spaces:

SELECT ACTNUMBR_1, ACTNUMBR_2, ACTNUMBR_3, 
    REPLACE(ACTNUMBR_1 + '-' + ACTNUMBR_2, ' ', '') AS Match_Account, 
    REPLACE(ACTNUMBR_1 + '-' + ACTNUMBR_2 + '-' + ACTNUMBR_3, ' ', '') AS Full_Account

answered Nov 16 '12 at 16:10

LittleBobbyTables - Au Revoir

32,008
25
109
114

Removing spaces in concatenated string column in a view

1 Answers1