CODEWITHSUNDEEP
mysqlstored-procedures
Ajay
Ajay

Reputation: 6590

MySql query to check string with %

I have two tables i.e. queues and indexqueuemaps.

Table queses:

Table indexqueuemaps

Datafrom queses

Id     Name
1      Pricing
2      Return
3      EDI

Datafrom indexqueuemaps

PhysicalQueueId      ConditionFieldValue
1                    Member not on file.
2                    Data is too long for MC/400 Field in XML Element: 20111109-20111113. Data is 17 long and the Max Length is 8
3                    Data is too long for MC/400 Field in XML Element: 0001008077. Data is % long and the Max Length is 9

This is my query 

SELECT `Name` FROM queues WHERE Id  IN 
(SELECT PhysicalQueueId FROM indexqueuemaps 
WHERE ConditionFieldValue = 'Data is too long for MC/400 Field in XML Element: 20111109-20111113. Data is 17 long and the Max Length is 8');

When I run this query it return me Name = Return. What I want to do here is if I check CoditionFieldValue = Data is too long for MC/400 Field in XML Element: 20111109-20111113. Data is 171 long and the Max Length is 8

instead of

Data is too long for MC/400 Field in XML Element: 20111109-20111113. Data is 17 long and the Max Length is 8

When I run the query it doesn't return any value because there is no any ConditionFieldValue is there in table. What I want to check is if such condition occurs and if there is Data is too long for MC/400 Field in XML Element: 0001008077. Data is % long and the Max Length is 9 this value in table query should return EDI

Suppose my query is 

SELECT `Name` FROM queues WHERE Id  IN 
(SELECT PhysicalQueueId FROM indexqueuemaps 
WHERE ConditionFieldValue = 'Data is too long for MC/400 Field in XML Element: 20111109-20111113. Data is 171 long and the Max Length is 8');

The result of query should be Name = EDI

How can I implement this?

EDIT

I have tried this query : 

Set @msg = 'Data is too long for MC/400 Field in XML Element: 20111109-20111113. Data is 171 long and the Max Length is 8';

SELECT q.NameFROM queues q inner join indexqueuemaps iq on q.Id = iq.PhysicalQueueId WHERE (iq.ConditionFieldValue = @msg) OR (iq.ConditionFieldValue != @msg AND @msg like 'Data is too long for MC/400 Field in XML Element: 20111109-20111113. Data is %' and iq.ConditionFieldValue like '% long and the Max Length is 8');

in this query I am checking

@msg like 'Data is too long for MC/400 Field in XML Element: 20111109-20111113. Data is %' and iq.ConditionFieldValue like '% long and the Max Length is 8'

I am checking it with hard coded string. But I want to check it with match value from table which contains text before % and after %. Simple I want to split the string from ConditionFieldValue column which contains % symbol.

Upvotes: 0

Views: 37

Answers (1)

Adrian Nasui
Adrian Nasui

Reputation: 1095

I think you are looking for a join between the two tables on the Id and PhysicalQueueId columns.

SELECT q.`Name` FROM queues q inner join indexqueuemaps iq on q.Id = iq.PhysicalQueueId 
WHERE 
(iq.ConditionFieldValue = @msg) OR 
(iq.ConditionFieldValue != @msg AND @msg like 'Data is too long for MC/400 Field in XML Element: 20111109-20111113. Data is %' 
and @msg like '% long and the Max Length is 8')

Upvotes: 1

Related Questions