SQL REPLACE function, how to replace single letter

My code is as follows:

REPLACE(REPLACE(cc.contype,'x','y'),'y','z') as ContractType,

This REPLACE's correctly what I would like, but it unfortunatley changes all "z's" to "y's" when I would like

x > y

y > z

Does this make sense? I would not like all of the new Y's to then change again in my second REPLACE function. In Microsoft Access, I would do this with the following

Iif(cc.contype = x, y, iif(cc.contype = y, x))

But I am not sure how to articulate this in SQL, would it be best I do this kind of thing in the client side language?

Many thanks.

EDIT: H开发者_如何转开发ave also tried with no luck:

       CASE WHEN SUBSTRING(cc.contype, 1, 1) = 'C'
        THEN REPLACE(cc.contype, 'C', 'Signed')
       CASE WHEN SUBSTRING(cc.contype, 1, 1) = 'E'
        THEN REPLACE(cc.contype, 'E', 'Estimate') as ContractType,

---

Try doing it the other way round if you don't want the new "y"'s to become "z"'s:

REPLACE(REPLACE(cc.contype,'y','z'),'x','y') as ContractType

---

Not that I'm a big fan of the performance killing process of handling sub-columns, but it appears to me you can do that just by reversing the order:

replace(replace(cc.contype,'y','z'),'x','y') as ContractType,

This will transmute all the y characters to z before transmuting the x characters to y.

---

If you're after a more general solution, you can do unioned queries like:

select 'Signed: ' || cc.contype as ContractType
    wherecc.contype like 'C%' from wherever
union all select 'Estimate: ' || cc.contype as ContractType
    where cc.contype like 'E%' from wherever

without having to mess about with substrings at all (at the slight cost of prefixing the string rather than modifying it, and adding any other required conditions as well, of course). This will usually be much more efficient than per-row functions.

Some DBMS' will actually run these sub-queries in parallel for efficiency.

---

Of course, the ideal solution is to change your schema so that you don't have to handle sub-columns. Separate the contype column into two, storing the first character into contype_first and contype_rest.

Then whenever you want the full contype:

select contype_first || contype_rest ...

For your present query, you could then use a lookup table:

lookup_table:
    first char(1) primary key
    description varchar(20)

containing:

first  description
-----  -----------
C      Signed: 
E      Estimate:

and the query:

select lkp.description || cc.contype_rest
    from lookup_table lkp, real_table cc
    where lkp.first = cc.first ...

Both these queries are likely to be blazingly fast compared to one that does repeated string substitutions on each row.

Even if you can't replace the single column with two independent columns, you can at least create the two new ones and use an insert/update trigger to keep them in sync. This gives you the old way and a new improved way for accessing the contype information.

And while this technically violates 3NF, that's often acceptable for performance reasons, provided you understand and mitigate the risks (with the triggers).

---

How about REPLACE(REPLACE(REPLACE(cc.contype,'x','ahhhgh'),'y','z'),'ahhhgh','y') as ContractType,

ahhhgh can be replaced with whatever you like.