?
Solved

SQL Query using dbo.fnsplit and LIKE operator

Posted on 2014-08-18
8
Medium Priority
?
289 Views
Last Modified: 2016-04-12
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
0
Comment
Question by:databarracks
  • 5
  • 3
8 Comments
 
LVL 36

Expert Comment

by:ste5an
ID: 40267667
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
 

Author Comment

by:databarracks
ID: 40267676
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
 
LVL 36

Expert Comment

by:ste5an
ID: 40267681
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:databarracks
ID: 40267718
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
 

Author Comment

by:databarracks
ID: 40267750
Actually I think this works, could you see anything wrong with this approach. The results look correct?
0
 
LVL 36

Accepted Solution

by:
ste5an earned 2000 total points
ID: 40267777
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
 

Author Comment

by:databarracks
ID: 40267838
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
 

Author Comment

by:databarracks
ID: 40267846
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question