CODEWITHSUNDEEP
sqlsql-server
bmw0128
bmw0128

Reputation: 13698

SQL Server Select 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?

Upvotes: 1

Views: 80

Answers (4)

Martin Smith
Martin Smith

Reputation: 453057

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.

Upvotes: 3

SQLMenace
SQLMenace

Reputation: 134961

A simple REPLACE will do

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

Upvotes: 0

a'r
a'r

Reputation: 36999

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

Upvotes: 8

HLGEM
HLGEM

Reputation: 96552

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

Upvotes: 3

Related Questions