SQL Server Select Question

Question

I have a query, say Select foo from bar Foo is a string field, and will always start with "http://". I want to replace "http://" with "xml://" during the select, so all foo values come back as xml://..., instead of http://... Is there a way to substitute on the fly, during the query?

Answer 1

Look at the REPLACE keyword. Alternatively if you need to do more complex porcessing than replace can handle, Look at CASE.

Answer 2

SELECT REPLACE(column, 'http://', 'xml://') FROM ...

Answer 3

A simple REPLACE will do

SELECT REPLACE(YourColumn,'http://','xml://') FROM YourTable

Answer 4

As you know it's always at the beginning at the string and presumably have integrity constraints to verify this!

SELECT STUFF(column,1,4,'xml') FROM ...

Edit: In fact why are you storing the protocol at all in that case? You could just store the rest of the URL and append whatever protocol you need without having to strip out a superfluous substring.