String concatenation driving me nuts

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

String concatenation driving me nuts

Author

Topic

vmon
Yak Posting Veteran

63 Posts

Posted - 2007-03-19 : 15:51:37

I need to concatenate a string to a column that contains free form data entered through a user interface screen. Problem I have is the free form data in a Text datatype can contain any characters. In this case usually dimensional data such as: 1/4" X 2' X 12'

I need to concatenate a varchar datatype sting of characters to the end of a Text datatype. What I am really struggling with the quote and double qoute in the Text column. Is there a way to treat the column as one set of characters and concatenate an additional string to it without all the quote and double quotes being evaluated? How do I handle the Varchar and Text datatypes? I thought this would be a simple Update statement but I think I have underestimated this.

thanks
vmon

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-03-19 : 15:58:54

just replace ' with ''

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

vmon
Yak Posting Veteran

63 Posts

Posted - 2007-03-19 : 17:13:34

I am getting error for unclosed quote. Here is example where I am trying to concatenate the word test
UPDATE dbo.tblSize s SET s.Notes = '' + s.Notes + ', ' + @text + '' Where OrderNo = '00007940' AND ReleaseNo = '1'

Notes = SPECIFY 2/16'
@text = Test

Which would look like this:
UPDATE dbo.stpMpOrderReleases SET Notes = ''SPECIFY 2/16', test'' Where OrderNo = '00007940' AND ReleaseNo = '1'

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-03-19 : 17:18:23

UPDATE dbo.tblSize s SET s.Notes = 'SPECIFY 2/16'', test' Where OrderNo = '00007940' AND ReleaseNo = '1'

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-03-19 : 17:44:00

have you even tried running it?
what do you get if you select that data?

'' means that when you update the data it will escape the '. so in your table you'd stil have only single '.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

vmon
Yak Posting Veteran

63 Posts

Posted - 2007-03-19 : 18:08:46

Cool! I got it thanks so much.
vmon

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources