SQL Query using dbo.fnsplit and LIKE operator

Hi Guys,

I am currently using dbo.fnsplit function in my database and works well with most of my queries. However I was wondering how I could use this function with LIKE.

Currently I have the below query but keep getting column name doesn't exist:

select * from Calls p inner join
(select * from dbo.fnSplit('75441222', ';')) Names
on p.OriginationNumber LIKE '%' + Names.OriginationNumber + '%'

Open in new window


Could someone please kindly show me how I can use this function with like, your help is much appreciated
databarracksAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
It depends on the definitin of you spilt function. But the split functions I know, return a value column. E.g.

SELECT  *
FROM    Calls p
        INNER JOIN ( SELECT *
                     FROM   dbo.fnSplit('75441222', ';')
                   ) Names ON p.OriginationNumber LIKE '%' + Names.value + '%';

Open in new window

0
databarracksAuthor Commented:
The definition is below@

USE [MyDB]
GO
/****** Object:  UserDefinedFunction [dbo].[fnSplit]    Script Date: 18/08/2014 14:15:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[fnSplit](
    @sInputList VARCHAR(8000) -- List of delimited items
  , @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items
) RETURNS @List TABLE (item VARCHAR(8000))

BEGIN
DECLARE @sItem VARCHAR(8000)
WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
 BEGIN
 SELECT
  @sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),
  @sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))
 
 IF LEN(@sItem) > 0
  INSERT INTO @List SELECT @sItem
 END

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

Open in new window

0
ste5anSenior DeveloperCommented:
Then it's item :)

SELECT  *
FROM    Calls p
        INNER JOIN ( SELECT *
                     FROM   dbo.fnSplit('75441222', ';')
                   ) Names ON p.OriginationNumber LIKE '%' + Names.item+ '%';

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

databarracksAuthor Commented:
Hi ste5an,

You are right that is working now but could I do an OR statement within your code.Basically I need to find out if the list of numbers are also in the DestinationNumber. The code below I know won't work could you please show me how to do an or and LIKE with the example you gave me

SELECT  *
FROM    Calls p
        INNER JOIN ( SELECT *
                     FROM   dbo.fnSplit('75441222, 78456654', ';')
                   ) Names ON p.OriginationNumber LIKE '%' + Names.item+ '%' OR  p.DestinationNumber LIKE '%' + Names.item;

Thanks you again
0
databarracksAuthor Commented:
Actually I think this works, could you see anything wrong with this approach. The results look correct?
0
ste5anSenior DeveloperCommented:
As far as I understand you:

SELECT  *
FROM    Calls p
WHERE   p.OriginationNumber LIKE '%75441222%'
        OR p.DestinationNumber LIKE '%78456654%';

SELECT  *
FROM    Calls P
        INNER JOIN ( SELECT LTRIM(RTRIM(item)) AS item
                     FROM   dbo.fnSplit('75441222, 78456654', ',')
                   ) N ON p.OriginationNumber LIKE '%' + N.item + '%'
                          OR p.DestinationNumber LIKE '%' + N.item + '%';

Open in new window


Can you use IN instead? Would be faster, cause LIKE approach here is not sargeable, thus not the fastest.

p.s. PLEASE use code tags, it is quite simple.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
databarracksAuthor Commented:
Hi,

I simply forgot to put the code inside the code tags....my bad I am aware of this feature in EE.

I normally use IN however that would only work if the source string and destination string for the numbers were in the same format.
Unfortunately this isn't the case as I still have to trim the leading zeros of my the source numbers in order to use LIKE.
0
databarracksAuthor Commented:
The destination database also adds things like the country code etc. +44 or +1 before the number and the source only shows the number with leading zeros.

Basically it isn't consistent in how it stores some of the called numbers
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.