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
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!
Kristen, thanks!
thanks for that. thats working now