Link to home
Start Free TrialLog in
Avatar of Mani Pazhana
Mani PazhanaFlag 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
Avatar of Kyle Abrahams, PMP
Kyle Abrahams, PMP
Flag of United States of America image

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

Avatar of 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....
show only records with all words regardless of the order.
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%'
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, ','))
if I just use one work like BLOCK or FUSE, it is not returning any match...
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
Avatar of Kyle Abrahams, PMP
Kyle Abrahams, PMP
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Just following up to see if you need anything else.
Thanks a lot for your help.