Manipulate string in query resultset in sql server?

Is there a way to 'append' and manipulate a 'string' onto the beginning of
returned results in sql server.

For instance, one of my fields [URLCurrent] returns in the following format:

"/catamaranco/charter/bareboat/lagoon440_electric/index.asp"

However, I wish to remove the trailing string "/catamaranco" and
replace it with

"www.catamarans.com/charter/bareboat/lagoon440_electric/inde x.asp"


Is this actually possible?

I currently have the following sql:

SELECT UserPath.u_name, UserPath.URLFrom, UserPath.URLCurrent,
UserPath.DateTime, Source.Source, Broker.Broker

FROM UserPath INNER JOIN

t_user ON UserPath.u_id = t_user.u_id INNER JOIN

Broker ON t_user.u_BrokerID = Broker.BrokerID
INNER JOIN

Source ON t_user.u_SourceID = Source.SourceID

WHERE (UserPath.DateTime > CONVERT(DATETIME, '2004-12-31 12:00:00
pm', 102))

GROUP BY UserPath.DateTime, UserPath.u_name, UserPath.URLFrom,
UserPath.URLCurrent, UserPath.URLCurrent, Source.SourceID, Source.Source,

Broker.Broker


Thanks
Jason

Re: Manipulate string in query resultset in sql server?

jason@catamaranco.com wrote:
> Is there a way to 'append' and manipulate a 'string' onto the
> beginning of returned results in sql server.
>
> For instance, one of my fields [URLCurrent] returns in the following
> format:
>
> "/catamaranco/charter/bareboat/lagoon440_electric/index.asp"
>
> However, I wish to remove the trailing string "/catamaranco" and
> replace it with
>
>
> "www.catamarans.com/charter/bareboat/lagoon440_electric/inde x.asp"

Sure. use the REPLACE function.

>
>
> Is this actually possible?
>
> I currently have the following sql:
>
> SELECT UserPath.u_name, UserPath.URLFrom,
> UserPath.URLCurrent, UserPath.DateTime, Source.Source, Broker.Broker
>
> FROM UserPath INNER JOIN
>
> t_user ON UserPath.u_id = t_user.u_id
> INNER JOIN
>
> Broker ON t_user.u_BrokerID =
> Broker.BrokerID INNER JOIN
>
> Source ON t_user.u_SourceID =
> Source.SourceID
>
> WHERE (UserPath.DateTime > CONVERT(DATETIME, '2004-12-31
> 12:00:00 pm', 102))
>
> GROUP BY UserPath.DateTime, UserPath.u_name, UserPath.URLFrom,
> UserPath.URLCurrent, UserPath.URLCurrent, Source.SourceID,
> Source.Source,
>
> Broker.Broker
>
Why the huge GROUP BY???? I don't see any aggregate functions in your SELECT
clause. If you want to return distinct records, use DISTINCT:

SELECT DISTINCT ...,
REPLACE(URLCurrent,'/catamaranco','www.catamarans.com'
AS URLCurrent, ...


HTH,
Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: Manipulate string in query resultset in sql server?

wrote in message
news:eyB9%23uqHFHA.904@tk2msftngp13.phx.gbl...
> Is there a way to 'append' and manipulate a 'string' onto the beginning of
> returned results in sql server.
>
> For instance, one of my fields [URLCurrent] returns in the following
format:
>
> "/catamaranco/charter/bareboat/lagoon440_electric/index.asp"
>
> However, I wish to remove the trailing string "/catamaranco" and
> replace it with
>
> "www.catamarans.com/charter/bareboat/lagoon440_electric/inde x.asp"
>
>
> Is this actually possible?
>
> I currently have the following sql:
>
> SELECT UserPath.u_name, UserPath.URLFrom, UserPath.URLCurrent,
> UserPath.DateTime, Source.Source, Broker.Broker
>
> FROM UserPath INNER JOIN
>
> t_user ON UserPath.u_id = t_user.u_id INNER
JOIN
>
> Broker ON t_user.u_BrokerID = Broker.BrokerID
> INNER JOIN
>
> Source ON t_user.u_SourceID = Source.SourceID
>
> WHERE (UserPath.DateTime > CONVERT(DATETIME, '2004-12-31
12:00:00
> pm', 102))
>
> GROUP BY UserPath.DateTime, UserPath.u_name, UserPath.URLFrom,
> UserPath.URLCurrent, UserPath.URLCurrent, Source.SourceID, Source.Source,
>
> Broker.Broker
>
>
> Thanks
> Jason
>
>

SELECT
UserPath.u_name,
UserPath.URLFrom,
REPLACE(UserPath.URLCurrent,'/catamaranco','www.catamarans.c om'),
UserPath.DateTime,
Source.Source,
Broker.Broker
FROM
....

Re: Manipulate string in query resultset in sql server?

> Why the huge GROUP BY???? I don't see any aggregate functions in your
SELECT
> clause. If you want to return distinct records, use DISTINCT:
>
> SELECT DISTINCT ...,
> REPLACE(URLCurrent,'/catamaranco','www.catamarans.com'
> AS URLCurrent, ...

Or better yet, fix the table design so that duplicates are not possible.
Surely there are some candidate keys in each of the tables...