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

Mani Pazhana
Mani Pazhana used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Kyle AbrahamsSenior .Net Developer

Commented:
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

Author

Commented:
Thanks

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

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

Author

Commented:
show only records with all words regardless of the order.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
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 AbrahamsSenior .Net Developer

Commented:
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, ','))

Author

Commented:
if I just use one work like BLOCK or FUSE, it is not returning any match...

Author

Commented:
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?
Senior .Net Developer
Commented:
it's working here:
Sorry I had pasted from another solution where I had to do something similiar:
http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/Q_28381062.html

drop table #temp

select 'Some Description                 ' name into #temp
insert into #temp select '... Fuse block ...'
insert into #temp select '.. . Block Fuse . . . '
insert into #temp select '. . . Block . . . '
insert into #temp select '. . . Fuse . . . '
insert into #temp select 'Another Description'

declare @tester varchar(max)
-- change this to be whatever you want.
set @tester = 'fuse'

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

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

Open in new window

Kyle AbrahamsSenior .Net Developer

Commented:
Just following up to see if you need anything else.

Author

Commented:
Thanks a lot for your help.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial