Inserting text into field without removing existing data 2

[lesleycampbell]

I am trying to insert text into a field without deleting the existing text. I want to insert it at the beginning of the existing text string.

Example:

Existing text in field = 'Existing Data'
Text string to insert = 'UPDATE 123 - '

Desired result = 'UPDATE 123 - Existing Data'

Thank you!

 

[gmmastros]

Something like this....

Update YourTableName
Set    YourColumnName = 'UPDATE 123 - ' + YourColumnName
Where  (Some condition here)

Please note that if the value in the column is NULL, then it will still be null afterwards. If you want it to be 'UPDATE 123 - ' if the original value is NULL, then change the code above to:

Set YourColumnName = 'UPDATE 123 - ' + Coalesce(YourColumnName, '')



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[lesleycampbell]

Thank you! This is working but I have now run into a second issue. The field is limited to 50 characters. Some of the pre-existing data is causing the updated string to exceed those characters. Is there a way to only update the accepted number of characters?

 

[SkipVought]

Hey Lesley, how about an appropriate Thank You, for George?

Do you see those little purple stars? That would be appropriate indeed.

 

[gmmastros]

Skip,

I appreciate what you are trying to do, but I honestly don't care about the stars. A simple thank you is enough.

Lesley,

It's unclear what you want to do. If you want to drop the data from the end of the existing date, then you could do this:

Update YourTableName
Set    YourColumnName = [!]Left([/!]'UPDATE 123 - ' + YourColumnName[!], 50)[/!]
Where  (Some condition here)

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[lesleycampbell]

That is EXACTLY what I wanted to do. I'm sorry I was unclear. I am very new to SQL.

Thank you so much!! I didn't know about the stars, so sorry about that.

 

[SkipVought]

Stars are a way for other members to identify posts that are deemed to be helpful with regard to the thread.

 

[DaveInIowa]

Since you're truncating values over 50 characters in length, it may be useful to identify these values by ending them with a pseudo-ellipsis of periods in the last 3 characters. Here's an example...

DECLARE @prefix VARCHAR(50) = 'UPDATE 123 - ' -- 13 characters
DECLARE @ExistingField VARCHAR(50) = 'More than fifty characters combined. Z' -- 38 characters
DECLARE @AdjustedField VARCHAR(50) =
		CASE
		   WHEN LEN(@prefix + @ExistingField) <= 50 THEN @prefix + @ExistingField
		   ELSE LEFT(@prefix + @ExistingField, 47) + '...'
		END
SELECT LEN(@prefix + @ExistingField) TotalLength, @ExistingField ExistingField, @AdjustedField AdjustedField

 

[Olaf Doschke]

Actually the data simply is trimmed by the field length, if too long. Also in INSERTS.
You would be able to do the original UPDATE without manually trimming with LEFT() and SQL Server wouldn't error or even warn about lost data, if 'UPDATE 123 - ' + YourColumnName is longer than 50 chars.

Bye, Olaf.