question

user-194 (google) avatar image
user-194 (google) asked

creating a user defined function using the key word LIKE

I am trying create a user function that will pass a order number and retrieve 3 simple select statements. However, the order number has a four digit sequence number attached to it and the order number that I am passing sometimes won't match because the sequence number might gave been updated. For example, order number 'P09994240000' will come into the order table, but once it hits the processing table, it could be 'P09994240002'. But I can only pass the initial order number at the time. I am trying to use the LIKE key word in the following statement-

USE ECOMLIVE                    
                    
go                    
CREATE FUNCTION fx_PROCESSING_ORDERHEADER_ORDERDETAIL                    
( @FULLORDERNO nvarchar(12))                    
RETURNS table                    
AS                    
RETURN (                    
        SELECT *                     
        FROM PROCESSING                    
        WHERE FULLORDERNO like @FULLORDERNO                    
    	 )                    
go                    

I'm new to sql, so please don't laugh. Could I use the substring function instead of like?

CREATE FUNCTION fx_PROCESSING_ORDERHEADER_ORDERDETAIL                    
( @FULLORDERNO nvarchar(12))                    
RETURNS table                    
AS                    
RETURN (                    
        SELECT *                     
        FROM PROCESSING                    
        WHERE substring(FULLORDERNO,1,8) = substring (@FULLORDERNO,1,8)                    
    	 )                    
sql-server-2005user-defined-functionlike
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Benjamin avatar image
Benjamin answered

The problem with the LIKE statement is that you are not using the wildcard (%) and that you are passing the entire string instead of just the portion you are trying to match. Without using the wildcard, you are doing the same thing as the equal sign.

So call the function like this (sending only the first 8 characters)...

fx_PROCESSING_ORDERHEADER_ORDERDETAIL( LEFT ( FULLORDERNO, 8 ) )            

...and run the match with the LIKE in this way:

SELECT *            
FROM PROCESSING            
WHERE FULLORDERNO like @FULLORDERNO + '%'            

Another method as you pointed out would be to match only on the first 8 characters within the function. So you would call the function like you normally do and then run this SELECT within the function:

SELECT *            
FROM PROCESSING            
WHERE LEFT(FULLORDERNO, 8) = LEFT(@FULLORDERNO, 8)            
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Madhivanan avatar image
Madhivanan answered

Note that you can rewrite this

WHERE substring(FULLORDERNO,1,8) = substring (@FULLORDERNO,1,8)            

with

WHERE FULLORDERNO  like substring (@FULLORDERNO,1,8)+'%'            

if you want to use the index if defined

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.