Buy Questpond's video subscriptions on huge discount.
DotNetFunda.com
Online: 2761
Home > Forums > Sql Server >

Pattern matching any where in the string

Posted by Mans.Oracle under Sql Server on 8/7/2013 | Points: 10 | Views : 1809 | Status : [Member] | Replies : 11
Write New Post | Search Forums | AnsweredResolved Posts | Un Answered Posts | Forums Home 
Hi All,
i have one table containing 300 records that records i am matching with passing the parameter in Sp the query is like in SP

SELECT @LRNO AS LRNO, a.SPECIAL_FEATURE_CODE, @CLASS AS CLASS, a.VALUE
FROM TASK0286_CLASSNOTATION a
INNER JOIN (SELECT @PASSED_CLASS_NOTATION AS CLASS_NOTATION) b ON LTRIM(RTRIM(b.CLASS_NOTATION)) LIKE '%' + LTRIM(RTRIM(a.VALUE)) + '%'
WHERE CLASS = @CLASS

simplifie here,

SELECT a.SPECIAL_FEATURE_CODE, CLASS, a.VALUE
FROM TASK0286_CLASSNOTATION a
INNER JOIN (SELECT 'IMO II' AS CLASS_NOTATION) b ON LTRIM(RTRIM(b.CLASS_NOTATION)) LIKE '%' + LTRIM(RTRIM(a.VALUE)) + '%'
WHERE CLASS = 'BV'

this query should return IMO II match but is returning IMO I also like that if i search BWE-EP is returning BWE-E also. please suggest me the way how to do.
would be great help if you write

Thanks,
mans.oracle



Reply | Reply with Attachment
Alert Moderator 

Responses

Posted by: Bandi on: 8/7/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
There is no need of INNER JOIN and You are checking condition in reverse. That's why incorrect results came into picture...
Try with the following query
-- Sample table
CREATE TABLE TASK0286_CLASSNOTATION1 ( SPECIAL_FEATURE_CODE VARCHAR(10), CLASS VARCHAR(10), VALUE VARCHAR(10))
insert into TASK0286_CLASSNOTATION1 values ( 'Special', 'BV', 'BWE-EP'),
( 'Special2', 'BV', 'IMO II'),
( 'Special3', 'BV', 'IMO I'),
( 'Special4', 'BV', 'BWE-E') 

-- Parameter to be passed in SP

DECLARE @CLASS_NOTATION VARCHAR(20) = 'IMO II';

--Query

SELECT a.SPECIAL_FEATURE_CODE, CLASS, a.VALUE 

FROM TASK0286_CLASSNOTATION1 a 

WHERE  LTRIM(RTRIM(a.VALUE)) LIKE '%' + LTRIM(RTRIM(@CLASS_NOTATION)) +'%'

AND CLASS = 'BV'

DROP TABLE TASK0286_CLASSNOTATION1

/* OUTPUT:
SPECIAL_FEATURE_CODE CLASS VALUE
Special2 BV IMO II*/

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Mans.Oracle, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Mans.Oracle on: 8/7/2013 [Member] Starter | Points: 25

Up
0
Down
Hi Chandu,
i appreciate your precious time on this but it workes as my inner join worked. here i am giving more intro about my request.

i have table containing the data like :-

BWE
BWT
IMO I
IMO III
IMO II
CPS(WBT)
CPS(COT)
CSR
ESP
BWM-E
BWM-EP
in water survey

and i wrote the code base on your suggetion

ALTER PROCEDURE [dbo].[spClassNotationMining1]
@LRNO VARCHAR(7),
@NEW_SEQ VARCHAR(2),
@CLASS VARCHAR(2),
@PASSED_CLASS_NOTATION VARCHAR(1050)
AS
BEGIN
SELECT a.SPECIAL_FEATURE_CODE, CLASS, a.VALUE
FROM TASK0286_CLASSNOTATION a
WHERE LTRIM(RTRIM(@PASSED_CLASS_NOTATION)) LIKE '%' + LTRIM(RTRIM(A.VALUE)) +'%'
AND CLASS = @CLASS
END

EXEC spClassNotationMining1 '9292436','00','BV','IMO II '

output:-
IMO I
IMO II

here out put is coming with IMO I IMO II both but we require only IMO II like that if i search for BWM-EP gives both BWM-EP BWM-E.

please if posible give me solution as my dead line is very near to solve.

Thanks,
mans.oracle

Mans.Oracle, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Ssj_Kumar on: 8/8/2013 [Member] Starter | Points: 25

Up
0
Down
try this you will get the required output

ALTER PROCEDURE [dbo].[spClassNotationMining1]
@LRNO VARCHAR(7),
@NEW_SEQ VARCHAR(2),
@CLASS VARCHAR(2),
@PASSED_CLASS_NOTATION VARCHAR(1050)
AS
BEGIN
SELECT a.SPECIAL_FEATURE_CODE, CLASS, a.VALUE
FROM TASK0286_CLASSNOTATION a
WHERE LTRIM(RTRIM(A.VALUE)) LIKE '%' + LTRIM(RTRIM(@PASSED_CLASS_NOTATION)) +'%'
AND CLASS = @CLASS
END

Regards,
Jayakumar Selvakani

Mans.Oracle, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Mans.Oracle on: 8/8/2013 [Member] Starter | Points: 25

Up
0
Down
Hi Jay ,
not working.

Mans.Oracle, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Bandi on: 8/8/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Simple modification...
--- Notice that I removed % symbol at the end...
-- Query 1: 
WHERE LTRIM(RTRIM(@PASSED_CLASS_NOTATION)) LIKE '%' + LTRIM(RTRIM(A.VALUE)) +''


That means you are searching for a.Value ending with the @PASSED_CLASS_NOTATION value. so the above query will give only "IMO II" .
If you want to search the exact @PASSED_CLASS_NOTATION in a.VALUE field, then use the below:
-- Query2: 
WHERE LTRIM(RTRIM(@PASSED_CLASS_NOTATION)) =  LTRIM(RTRIM(A.VALUE))


For the below sample data,
( 'Special2', 'BV', 'safIMO II'),
( 'Special2', 'BV', 'IMO I'),
( 'Special2', 'BV', 'IMO III'),
( 'Special2', 'BV', 'IMO II'),

First query will returns 'safIMO II', 'IMO II'
Second query will give 'IMO II'

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Mans.Oracle, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Ssj_Kumar on: 8/8/2013 [Member] Starter | Points: 25

Up
0
Down
Can you run the query without where condition and provide me the result

Regards,
Jayakumar Selvakani

Mans.Oracle, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Bandi on: 8/8/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Hi Jayakumar, 
CREATE TABLE TASK0286_CLASSNOTATION1 ( SPECIAL_FEATURE_CODE VARCHAR(10), CLASS VARCHAR(10), VALUE VARCHAR(10)) 

insert into TASK0286_CLASSNOTATION1 values ( 'Special', 'BV', 'BWE-EP'), 

( 'Special2', 'BV', 'IMO I'),  

( 'Special2', 'BV', 'IMO III'),  

( 'Special2', 'BV', 'IMO II'),  

( 'Special3', 'BV', 'IMO I'), 

( 'Special4', 'BV', 'BWE-E'), 

( 'Special2', 'BV', 'BWE'),  

( 'Special2', 'BV', 'BWT'),  

( 'Special2', 'BV', 'CPS(WBT)'),  

( 'Special2', 'BV', 'CPS(COT)'),  

( 'Special2', 'BV', 'CSR'),  

( 'Special2', 'BV', 'ESP'),  

( 'Special2', 'BV', 'BWM-E'),  

( 'Special2', 'BV', 'BWM-EP') 

GO

ALTER PROCEDURE [dbo].[spClassNotationMining1] 

@LRNO VARCHAR(7), 

@NEW_SEQ VARCHAR(2), 

@CLASS VARCHAR(2), 

@PASSED_CLASS_NOTATION VARCHAR(1050) 

AS 

BEGIN 

SELECT a.SPECIAL_FEATURE_CODE, CLASS, a.VALUE 

FROM TASK0286_CLASSNOTATION1 a 

WHERE LTRIM(RTRIM(A.VALUE)) LIKE '%' + LTRIM(RTRIM(@PASSED_CLASS_NOTATION)) +'%' 

AND CLASS = @CLASS 

END 

GO

EXEC spClassNotationMining1 '9292436','00','BV','IMO II' 

GO

DROP TABLE TASK0286_CLASSNOTATION1

/*OUTPUT:
SPECIAL_FEATURE_CODE CLASS VALUE
Special2 BV IMO III
Special2 BV IMO II*/
Check the above code... You will get both IMO III, IMO II records.. But OP wants to get only "IMO II"

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Mans.Oracle, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Mans.Oracle on: 8/8/2013 [Member] Starter | Points: 25

Up
0
Down
Hi All,
it's done by me .thnx for all your time..


ALTER PROCEDURE [dbo].[spClassNotationMining]
@LRNO VARCHAR(7),
@NEW_SEQ VARCHAR(2),
@CLASS VARCHAR(2),
@PASSED_CLASS_NOTATION VARCHAR(1050)


AS
BEGIN

SELECT @LRNO AS LRNO, a.SPECIAL_FEATURE_CODE, @CLASS AS CLASS, a.VALUE
FROM TASK0286_CLASSNOTATION A
INNER JOIN (SELECT @PASSED_CLASS_NOTATION AS CLASS_NOTATION) b
ON LTRIM(RTRIM(b.CLASS_NOTATION)) like '%[^a-z0-9!@#$%¨&*()-=+/*.{}]' + LTRIM(RTRIM(a.VALUE)) + '[^a-z0-9!@#$%¨&*()-=+/*.{}]%'
OR LTRIM(RTRIM(b.CLASS_NOTATION)) like LTRIM(RTRIM(a.VALUE)) + '[^a-z0-9!@#$%¨&*()-=+/*.{}]%'
OR LTRIM(RTRIM(b.CLASS_NOTATION)) like '%[^a-z0-9!@#$%¨&*()-=+/*.{}]' + LTRIM(RTRIM(a.VALUE))
OR LTRIM(RTRIM(b.CLASS_NOTATION)) = LTRIM(RTRIM(a.VALUE))
WHERE CLASS = @CLASS
END

Mans.Oracle, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Bandi on: 8/8/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Your query is not looking good... and too many OR & LIKE conditions are there.. You will face performance issues for large sets of data...
Can you tell us the exact requirement.. We will try to give you the optimized query

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Mans.Oracle, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Mans.Oracle on: 8/8/2013 [Member] Starter | Points: 25

Up
0
Down
Hi Chandu,
yes, you may be right but as of now we have only 300 data into table. i have meeting today with my manager lets see if any concern then will touch you.

Thanks,
mans.oracle

Mans.Oracle, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Bandi on: 8/8/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Ok You finish your meeting....
LTRIM(RTRIM(b.CLASS_NOTATION)) like '%[^a-z0-9!@#$%¨&*()-=+/*.{}]' + LTRIM(RTRIM(a.VALUE)) + '[^a-z0-9!@#$%¨&*()-=+/*.{}]%'
OR LTRIM(RTRIM(b.CLASS_NOTATION)) like LTRIM(RTRIM(a.VALUE)) + '[^a-z0-9!@#$%¨&*()-=+/*.{}]%'
OR LTRIM(RTRIM(b.CLASS_NOTATION)) like '%[^a-z0-9!@#$%¨&*()-=+/*.{}]' + LTRIM(RTRIM(a.VALUE))
OR LTRIM(RTRIM(b.CLASS_NOTATION)) = LTRIM(RTRIM(a.VALUE))
means you should match with "IMO II" and on both sides of the Passed_Class_Notation side you can't have any one of these symbols"a-z0-9!@#$%¨&*()-=+/*.{}" right? That is what you are trying?
what should be your output for VALUE which has other those symbols such as <, >, ~ ?


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Mans.Oracle, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response

Latest Posts