Space separated string to create dynamic Sql query

Try this SQL User Defined Function to Parse a Delimited String helpful .

For Quick Solution use this ,

SELECT PARSENAME(REPLACE('ABC DataServices Pvt Ltd', ' ', '.'), 2) // return DataServices

PARSENAME takes a string and splits it on the period character. It takes a number as it's second argument, and that number specifies which segment of the string to return (working from back to front). you can put the index you want in place of 2 in above like

SELECT PARSENAME(REPLACE('ABC DataServices Pvt Ltd', ' ', '.'), 3)  --return Pvt

declare a string in your store procedure and use set this value to it. use where you want. The only problem is when the string already contains a period. One thing should be noted that PARSENAME only expects four parts, so using a string with more than four parts causes it to return NULL

Try this function:

CREATE FUNCTION [dbo].[fnSplitString] 
( 
@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

And use it like this:

select * from dbo.fnSplitString('Querying SQL Server','')

This will scale to any number of search terms and does not require dynamic SQL.

declare @a table (w varchar(50)) -- holds original string
declare @b table (s varchar(50)) -- holds parsed string

insert into @a -- load string into temp table
values ('ABC DataServices Pvt Ltd')

--parse string as XML
;WITH Vals AS (
        SELECT  w,
                CAST('<d>' + REPLACE(w, ' ', '</d><d>') + '</d>' AS XML) XmlColumn
        FROM    @a
)


--Insert results to parsed string
insert into @b
SELECT  
        C.value('.','varchar(max)') ColumnValue
FROM    Vals
CROSS APPLY Vals.XmlColumn.nodes('/d') AS T(C)


--Join on results
select * from companytable
join @b b on b.s like '%'+companyname+'%'

You can also try this.

First in your parameter's value replace space with comma (,) and then replace comma with desired where condition like this -

DECLARE @companyname VARCHAR(1000)
    ,@where VARCHAR(max)

SET @companyname = 'ABC DataServices Pvt Ltd'
SET @companyname = replace(@companyname, ' ', ', ')

SELECT @where = 'companyname like ''%' + 
REPLACE(@companyname, ', ', '%'' OR companyname like ''%') + '%'''

PRINT @where

PRINT ('SELECT * FROM CompanyTable WHERE' + @where)
--EXEC ('SELECT * FROM CompanyTable WHERE' + @where)