Use SPLIT_STRING with OPENROWSET

Question

Is there a way to pass the result from OPENROWSET BULK into a variable so i can use STRING_SPLIT to divide it into rows?

Something like:

    DECLARE @MyString = FROM OPENROWSET (BULK 'C:\Temp\File.csv', SINGLE_CLOB) MyFile

SELECT * FROM STRING_SPLIT ( @MyString , '\n' )

Answer 1

Here is one solution.

I used REPLACE to replace the CR/LF with a single character I knew would not be in my file (tilde) and then used that in STRING_SPLIT

DECLARE @CrLf CHAR(2) = CHAR(13) + CHAR(10)
DECLARE @BulkColumn VARCHAR(max)

SELECT @BulkColumn = replace(BulkColumn, @CrLf, '~')
FROM OPENROWSET(BULK 'C:\Temp\z.txt', SINGLE_CLOB) MyFile

SELECT *
FROM string_split(@BulkColumn, '~')

---

| value |
|-------|
| one   |
| two   |
| three |