Add space before and after all items

Hi
i want to add spaces before and after a string
so string is 'hello' i want to it like ' hello '

whats the best way to do this

will also need to add in the '' as the string come in as hello

the problem im having is im using the following update

UPDATE dbo.SSCLXWorkingDataloadFile3
	SET SecurityName = REPLACE(SecurityName , @StringToFind,@StringReplacement)
	FROM dbo.SSCLXWorkingDataloadFile3
	WHERE SecurityName IS NOT NULL

but what happens is the string to find might be = hell and changes it to hells. but there might be a word like hello so it changes that to hellso. i want to stop this by adding spaces around the string

DECLARE @str VARCHAR(32) = 'Hello';
SELECT ' ' + @str + ' '

If there is a possibility that the string already has spaces,and you don't want to have more than one space, use LTRIM and RTRIM like this:

SELECT ' ' + LTRIM(RTRIM(@str)) + ' '

You probably don't need to add the single quotes, because the single quotes are required only in the literal representation. If the string in a variable is hello without the single quotes, you can use it as it is, without the single quotes.

If you do decide that you want to add single quotes, just double them up. For example,

DECLARE @str VARCHAR(32) = 'Hello';
SELECT '''' + @str + '''';
UPDATE dbo.SSCLXWorkingDataloadFile3
	SET SecurityName = REPLACE(SecurityName , ' ' + @StringToFind+ ' ',' ' + @StringReplacement + ' ')
	FROM dbo.SSCLXWorkingDataloadFile3
	WHERE SecurityName IS NOT NULL

This won't catch the @StringToFind if it is at the Start / End of the [SecurityName]

You could do

RTrim(LTrim(
    REPLACE(' ' + SecurityName  + ' ', ' ' + @StringToFind + ' ', ' ' + @StringReplacement + ' ')
    ))

but that will remove any pre-existing leading or trailing space - hopefully that wouldn't be a problem?

Recommend that the O/P also change the WHERE clause to

WHERE ' ' + SecurityName  + ' ' LIKE '% ' + @StringToFind + ' %'

that will only included NON-Null records, but will also only update rows where the @StringToFind is present. Otherwise every row in the table will be updated (including the ones with no actual change) which will use up log space, and take time to process, and if there is an Audit actions in a TRIGGER they will fire too - storing a "before/after" snapshot of something that hasn't actually changed!

1 Like

Kristen, thanks! :slight_smile:

thanks for that. thats working now