How to find and replace only first two hyphens in sql ex 'UTR-ATLAS-006-0143391-002'-CodePudding

How to find and replace only first two hyphens with a pipe | in SQL Server.

For example:
'UTR-ATLAS-006-0143391-002' becomes 'UTR|ATLAS|006-0143391-002'

CodePudding user response：

In an RDBMS that has the regexp functions this would be simple.

F.e. in Postgresql, by using a regex with 2 capture groups and lazy search

regexp_replace(str,'^(.*?)-(.*?)-','\1|\2|')

In Sql Server, without regex functions, it's a bit harder.

select str
, new_str = replace(left(str, charindex('-', str, charindex('-', str) 1)), '-', '|') 
                      right(str, len(str) - charindex('-', str, charindex('-', str) 1)) 
from (values
  ('UTR-ATLAS-006-0143391-002')
, ('NO DASHES IN THIS STRING')
) test(str)

str	new_str
UTR-ATLAS-006-0143391-002	UTR\|ATLAS\|006-0143391-002
NO DASHES IN THIS STRING	NO DASHES IN THIS STRING

CodePudding user response：

if this were DB2, you could use this:

with data (str) as (values
('UTR-ATLAS-006-0143391-002')
)
select regexp_replace(
           regexp_replace(str,'-','|', 1, 1),
           '-','|', 1, 1
       )
from   data

This would first replace the 1st occurrence of '-' and of the result again the first occurrence, which is actually the second in the string, giving:

UTR|ATLAS|006-0143391-002