Search code examples
sqlsql-servert-sqldatabase-administration

How can I separate/list words mentioned in varchar(max) column

I got this question in SQL Server developer interview. Can anyone help please?

If there is a column of type varchar(max) with a lot of words, how can I list the words?

Thanks

Solution

  • enter image description here

    select DepartmentID,Name, Split_Values
from HumanResources.Department
cross apply (SELECT value as Split_Values FROM STRING_SPLIT(Name, ' ')) as ca
where DepartmentID  in (12,14,9)

    We are able to split the data of one column as an Array / MultiValue using the STRING_SPLIT function.

    We executed the above query on AdventureWorks2019 database for DepartmentID 12, 14 and 9 and returning the expected result. Here 'name' is column with words and 'Split_Values' is the output of name column.