mercredi 30 mars 2016

How to replace instance of string in SQL Server when followed by another pattern?

I have a [message] column which can contain hostname information - which I need to replace and remove parts of.

The statement I wrote to do this was:

update  table1
set     message = replace(replace(message ,'RL','NN'),'.COMPANY.COM','')
where   message is not NULL

So a hostname that appears as - RL12345.COMPANY.COM will return as NN12345.

The issue is that if 'RL' appears anywhere else in the message column, it will be erroneously replaced. Is there a way to conditionally replace using a regex? I could verify that the number of numerical characters between RL and .COMPANY.COM was always between 7-9 for example.

To clarify, although RL will always be the beginning of the hostname string, it may not (and probably will not) be the beginning of the entire string in the message column.

e.g.:

"Tried to access RL12345.COMPANY.COM with no success"

There may also be multiple hostname instances in the one cell, all instances must be tansformed.

Aucun commentaire:

Enregistrer un commentaire