concatenate and encode - SQL Server Forums

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)

concatenate and encode

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2006-05-25 : 12:45:33

2 questions

1. how do I concatenate in sql -- meaning I have a bunch of text and variables to concatenate as a string.
2. is there anyway to urlencode something in a string. (I am doing both to create a string of a URL I will be sending to with the data from teh db)

Kristen
Test

22859 Posts

Posted - 2006-05-25 : 15:04:03

(1)

SELECT [MyStuff] = Column1 + COlumn2 + Column3
FROM MyTable

If any of Column1 ... Column3 are NULL then the result will be NULL, if that's a potential problem then use:

SELECT [MyStuff] = COALESCE(Column1, '') + COALESCE(COlumn2, '') + COALESCE(Column3, '')
FROM MyTable

This won't work if the total length exceeds 8,000 characters (or 4,000 for Nvarchar) [in SQL2000]

(2) Don't think so - best to do in your application. You could do a bunch of nested REPLACE for a poor-man's urlencode if that would do?

Kristen

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2006-05-26 : 01:41:16

it's all done in a stored procedure and i'm trying to avoid going back to the server.in this case is mystuff an variable declared?
waht does coalsce do?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-26 : 01:55:57

[MyStuff] is just a column alias

from Books OnLine

quote:
COALESCE
Returns the first nonnull expression among its arguments.

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2006-05-26 : 03:30:05

can you help me ?
what is wrong with this?

select @url=@myurl + "?FirstName=" + COALESCE(firstname from leads where myid=@id, '') + "&LastName=" + COALESCE(firstname from leads where myid=@id, '')

I am trying to have firstname pull the first name from the record and lastname pull the last name from the record.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-05-26 : 03:37:17

aside from the source are both firstname, you don't need a subquery

quote:
Originally posted by esthera

select @url=@myurl + "?FirstName=" + COALESCE(firstname, '') + "&LastName=" + COALESCE(lastname, '') from leads where myid=@id

--------------------
keeping it simple...

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2006-05-26 : 03:46:15

i still get Incorrect syntax near the keyword 'from'.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-05-26 : 03:49:13

because you have double quotes surrounding your strings, should be single quote

--------------------
keeping it simple...

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2006-05-26 : 03:56:35

tried that -- still the same error

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-05-26 : 04:04:29

where did you apply the single quote?
you do realize this is spoon feeding? and learning only comes to you if you make the mistake and solve the problem on your own first, then if given a solution, be able to atleast try to understand it and not just blindly apply the solution?

select @url=@myurl + '?FirstName=' +
COALESCE(firstname, '') + '&LastName=' +
COALESCE(lastname, '') from leads where myid=@id

--------------------
keeping it simple...

Kristen
Test

22859 Posts

Posted - 2006-05-26 : 13:09:29

Note that

'&LastName='

should probably be

'&LastName='

if you want to use it in, say, and Anchor HREF.

Note that this will NOT validate


<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN"
   "http://www.w3.org/TR/html4/strict.dtd">
<HTML>
   <HEAD>
      <TITLE>My Title</TITLE>
   </HEAD>
   <BODY>
<p><a href="?FirstName=Fred&LastName=Bloggs">Fred Bloggs</a></p>
   </BODY>
</HTML>

whereas this will


<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN"
   "http://www.w3.org/TR/html4/strict.dtd">
<HTML>
   <HEAD>
      <TITLE>My Title</TITLE>
   </HEAD>
   <BODY>
<p><a href="?FirstName=Fred&amp;LastName=Bloggs">Fred Bloggs</a></p>
   </BODY>
</HTML>

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources