Split string by space

Question

I am trying to use a result of a string split to where close in my sql condition

I have table which has a varchar column. I am trying to filter the result where there is only one word is presented.

eg. if the table have values like 'ABC DEF','XYZ','EGF HIJ' and I am expecting to get only 'XYZ' as result.

I am not sure what to use here, though splitting the each value in column will be a one way. But not sure how I can use it as a condition

I had look some split samples like below.

DECLARE @Str VARCHAR(100) ='Test Word'

SELECT SUBSTRING(@Str , 1, CHARINDEX(' ', @Str ) - 1) AS [First],
       SUBSTRING(@Str , CHARINDEX(' ', @Str ) + 1, LEN(@Str )) AS [Last]

Answer 1

This is also work

select SUBSTRING(EmpName,0,CHARINDEX(' ',EmpName)),SUBSTRING(EmpName,CHARINDEX(' ',EmpName),LEN(EMPNAME)) 
from tblemployee

Answer 2

To get only 'XYZ' in the with a column containing

tableName.fieldName
'ABC DEF'
'XYZ'
'EGF HIJ' 

Do this

SELECT * 
FROM tableName
WHERE CHARINDEX(' ',fieldname) = 0