I have a table with multiple IP addresses in a single column separated by commas, I am using the following query and it works perfectly when used in a select, but when I try to make a view it doesn't work, it gives me the error

Invalid Object name 'string_split'.

Can string_split be actually used in views? I can use an SP but it would really help that its a view because I need to make multiple joins to this result with other views and a UNION ALL

SELECT 
      distinct [ColumnA]
      ,[ColumnB]
      ,cs.value as IPs
  FROM [table] as A
  cross apply string_split( 
    replace(
    replace(
            replace(
                replace([value], '-', ',')
                , ';', ',')
            , '_', ',')
            , ' ', ',')
    , ',')cs
  where A.[value] <> ''
  and cs.value like '%.%.%.%' --and cs.value like '%host%'
  order by 3

The data usually also has a lot of garbage text, like system- or other words so I filter them out with the replaces so the string_split also splits this and then I use where to get rid of everything that isn't an IP

Data example

Result example when using as a query

CodePudding user response：

Your query works fine in a view as follows. Note, SQL Server 2017 introduced Translate which you can use instead of the nested Replace functions

create or alter view test as 

select ColumnA, ColumnB, cs.[value] as IPs
from t
cross apply string_split( translate(IPs, '-;_ ', ',,,,'),',')cs
where cs.[value] <> ''
and cs.value like '%.%.%.%' 

GO

select * from test
order by IPs