Split a database table column value of type VARCHAR into multiple values using a delimiter?

It is definitely possible, and pretty straight-forward with STRING_SPLIT. Even before SQL Server 2017 when STRING_SPLIT was introduced it was possible, just not as pretty.

An example with your string would be something like this:

DECLARE @t TABLE (ID INT, StringVal VARCHAR(1000));

INSERT @t
VALUES (1, 'text|another|other');

SELECT *
FROM @t
CROSS APPLY STRING_SPLIT(StringVal, '|');

The results of this are

ID| StringVal          | value
--------------------------------
1 | text|another|other | text
1 | text|another|other | another
1 | text|another|other | other

So to answer your question, yes it is definitely possible. However, you should certainly take the comment discussion into consideration.

Well as Panagiotis Kanavos said as a comment, the best and easiest would be to this in the 'Transform' part of your ETL (Extract, Transform, Load). When you do this, you don't even have to deal with SQL : since you are on SQL-SERVER i assume you use SSIS. You can call an external C# or .Net script to do all the work.

To answer your question, yes SQL provides a lot of builtin function to deals with string : you can use substr() + charindex(), or String_split or even write your own

Microsoft doc about String Functions : https://learn.microsoft.com/en-us/sql/t-sql/functions/string-functions-transact-sql?view=sql-server-ver15

The short answer is: You can do it.

The longer answer is: You shouldn't do it, because it violates the principle of Database Normalization. Anything up to NF3 is the (arguably) bare minimum, which you should stick to.

If the data enters your database from an external source, Transform it in your ETL process before it actually enters your database.

Your database should then store the data in relational form, and other transformations to fit your frontend should be done upon retrival to make it fit your consuming applications. You can read up on the ANSI-SPARC Architecture if you so desire.

Please check the below answer. This might help :

DECLARE @Table TABLE (ID int, SampleData Varchar(100))

INSERT @Table
(ID,SampleData)
VALUES
(1, 'text|another|other'),
(2, 'sample|data|again'),
(3, 'this|should|help')


 SELECT 
     A.ID AS ID,  
     Split.a.value('.', 'VARCHAR(100)') AS SampleData  
 FROM  (
        SELECT 
            ID,  
            CAST ('<M>' + REPLACE(SampleData, '|', '</M><M>') + '</M>' AS XML) AS SampleData  
        FROM  
            @Table A
         )  AS A 
    CROSS APPLY SampleData.nodes ('/M') AS Split(a)

The Output:-

ID  SampleData
1   text
1   another
1   other
2   sample
2   data
2   again
3   this
3   should
3   help