How to use SQL query to make second line after character "|"?

Question

May I know how to use SQL select query to make new line after character ("|") ?

URL SQL

I think you want something like this: https://stackoverflow.com/questions/14328621/splitting-string-into-multiple-rows-in-oracle — Koen Lostrie, Feb 02 '21 at 09:58

score 0 · Answer 1 · answered Feb 02 '21 at 06:29

0

You can use REPLACE as follows:

replace(your_str,'|',chr(10))

db<>fiddle

answered Feb 02 '21 at 06:29

Popeye

35,427
4
10
31

Hi, i tried use replace but not successful due to after "|" is next record, i thinking use join could be can solve this, but i no idea how to use join query ? – Hoong wai kong Feb 02 '21 at 06:45

score 0 · Answer 2 · edited Feb 02 '21 at 08:10

0

If you mean new line in SQL you can try it

DECLARE @NewLineChar AS CHAR(2) = CHAR(13) + CHAR(10)

PRINT ('SELECT FirstLine AS MyFirstLine ' + @NewLineChar + 'SELECT SecondLine AS MySecondLine')

edited Feb 02 '21 at 08:10

jarlh

42,561
8
45
63

answered Feb 02 '21 at 06:29

Ramin Hbb

9
2

sorry, make you confusing, new line is next record. – Hoong wai kong Feb 02 '21 at 06:46
1

That's invalid for Oracle – Feb 02 '21 at 07:02

score 0 · Answer 3 · answered Feb 02 '21 at 10:12

you can also this table-value function. you can set your entire text into function as well as a delimiter that is "|" in your case, finally it return multiple records based on how many "|" was exist.

ALTER FUNCTION [dbo].[Split] ( @string NVARCHAR(MAX), @delimiter CHAR(1)) RETURNS @output TABLE(splitdata NVARCHAR(MAX)) 

BEGIN 
    DECLARE @start INT, @end INT 
    SELECT @start = 1, @end = CHARINDEX(@delimiter, @string) 
    WHILE @start < LEN(@string) + 1 BEGIN 
        IF @end = 0  
            SET @end = LEN(@string) + 1
       
        INSERT INTO @output (splitdata)  
        VALUES(SUBSTRING(@string, @start, @end - @start)) 
        SET @start = @end + 1 
        SET @end = CHARINDEX(@delimiter, @string, @start)
        
    END 
    RETURN 
END

If your problem wont solve feel free to ask again.

RaminHbb

How to use SQL query to make second line after character "|"?

3 Answers3