Separate with different characters sql

Question

So I have a column which contains multiple different strings. If the string contains a _ it has to be split on that character. For the others I use would use a separate rule like: If it starts with 4FH, GWO, CTW and doesn't have an _ then it has to split after 3 characters. If it starts with 4 and doesn't have an _.. etc..

Example

|Source    | 
|EC_HKT    |
|4FHHTK    |
|ABC_GJE   |
|4SHARED   |
|ETK_ETK-40|

etc..

What i want as a result is

|Source|Instance|
|EC    |HKT     |
|4FH   |HTK     |
|ABC   |GJE     |
|4     |SHARED  |
|ETK   |40      |

As a start I first tried

SELECT
    LEFT(lr.Source, CHARINDEX('_',lr.Source)) AS Source,
    RIGHT(lr.Source, LEN(lr.Source) - CHARINDEX('_', lr.Source)) AS Interface,

But this would only work if all the results had a _ . Any tips or ideas? Would a CASE WHEN THEN work?

Answer 1

This requires a little creativity and no doubt more work than what I've done here, however this gives you at least one pattern to work with and enhance as required.

The following uses a simple function to apply basic rules from your sample data to derive the point to split your string, plus some additional removal of characters and removal of the source part if it also exists in the instance part.

If more than one "rule" matches, it uses the one with higher number of matching characters.

Function:

create or alter function splitpos(@string varchar(50))
returns table as
return
(
    with map as (
        select * from (values ('4FH'),('GWO'),('CTW'),('4'))m(v)
    )
    select IsNull(NullIf(CharIndex('_',@string),0)-1,Max(Len(m.v))) pos
    from map m
    where @string like m.v + '%'
)

Query:

select l.v source, Replace(Replace(Replace(Stuff(source,1,pos,''),'_',''),'-',''),l.v,'') instance
from t
cross apply dbo.splitpos(t.source)
cross apply (values(Left(source,pos)))l(v)

Demo DB<>Fiddle