Avatar of Mani Pazhana
Mani Pazhana
Flag for United States of America asked on

SQL Server 2008 - Split text inside Stored procedure and build sql

Hello Experts,
Using SQL Server 2008 - Split text inside Stored procedure and build sql.

I have stored procedure that takes single input parameter as varchar(100).

Input parameter will have text something like this:

'FUSE  BLOCK'  <-----this is just a sample, will be more than 2 words...

'FUSE'

'BLOCK'


I want to split the Text based on single space in between and build like SQL;


IF 'FUSE  BLOCK'

  Select * from dbo.Part Where Name LIKE '%FUSE%' AND Name LIKE '%BLOCK%'


'FUSE'


  Select * from dbo.Part Where Name LIKE '%FUSE%'

'BLOCK'

  Select * from dbo.Part Where Name LIKE '%BLOCK%'


it should be dynamic based on the spilt characters...any idea how to do it?

Thanks
Microsoft SQL Server 2008Microsoft SQL Server 2005Microsoft SQL Server

Avatar of undefined
Last Comment
Mani Pazhana

8/22/2022 - Mon
Kyle Abrahams

select p.*
from part p
--tester is the variable you're splitting
join dbo.fn_txt_split(@tester, ' ') s on p.Name like '%' + s.item +'%'

Note that there are more efficient split methods for splitting larger sets of data, but I find this works well when you're splitting a variable.


Code for fn_txt_split:
/****** Object:  UserDefinedFunction [dbo].[fn_Txt_Split]    Script Date: 03/05/2014 13:45:54 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_Txt_Split]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn_Txt_Split]
GO

/****** Object:  UserDefinedFunction [dbo].[fn_Txt_Split]    Script Date: 03/05/2014 13:45:54 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




Create Function [dbo].[fn_Txt_Split]( 
    @sInputList varchar(8000) -- List of delimited items 
  , @Delimiter char(1) = ',' -- delimiter that separates items 
) 
RETURNS @list table (Item varchar(8000)) 
as begin 
DECLARE @Item Varchar(8000) 
  
  

WHILE CHARINDEX(@Delimiter,@sInputList,0) <> 0 
BEGIN 
SELECT 
@Item=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@Delimiter,@sInputList,0 
)-1))), 
@sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@Delimiter,@sInputList,0)+1,LEN(@sInputList)))) 
  
IF LEN(@Item) > 0 
INSERT INTO @List SELECT @Item 
  
END 

  
IF LEN(@sInputList) > 0 
INSERT INTO @List SELECT @sInputList -- Put the last item in 
  
return 
END 



GO 

Open in new window

Mani Pazhana

ASKER
Thanks

it is showing BLOCK or FUSE or BLOCK FUSE or FUSE BLOCK.

can we do just to show BLOCK FUSE  or FUSE BLOCK....
Mani Pazhana

ASKER
show only records with all words regardless of the order.
Your help has saved me hundreds of hours of internet surfing.
fblack61
Mani Pazhana

ASKER
can someone please give me example to loop the function and build SQL inside stored procedure..

I want to build something like this:

  Select * from dbo.Part Where Name LIKE '%FUSE%' AND Name LIKE '%BLOCK%'
Kyle Abrahams

You can:

declare @tester varchar(max)
set @tester = 'FUSE BLOCK'

--returns matches for each condition
;with CTE
as
(
select *, row_number() over (partition by name order by item) myRow
from part p
join dbo.fn_txt_split(@tester, ',') s on p.Name like '%' + s.item + '%'
)

--give me only the matches that work for ALL conditions
select descr from cte
group by name
having max(myRow) = (select count(*) from dbo.fn_txt_split(@tester, ','))
Mani Pazhana

ASKER
if I just use one work like BLOCK or FUSE, it is not returning any match...
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Mani Pazhana

ASKER
this is throwing error:

--give me only the matches that work for ALL conditions
select descr from cte
group by name
having max(myRow) = (select count(*) from dbo.fn_txt_split(@tester, ','))

what is descr?
ASKER CERTIFIED SOLUTION
Kyle Abrahams

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Kyle Abrahams

Just following up to see if you need anything else.
Mani Pazhana

ASKER
Thanks a lot for your help.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy