Click here to Skip to main content
14,367,980 members
Rate this:
Please Sign up or sign in to vote.
See more:
Hi all,

Table name: sp_text
Column name : obj_Text

In this column, all the stored procedures will be stored as text.

I need to retrieve all the stored procedures which has raiserror and %d in raiserror.

select * from sp_Text where obj_Text like '%raiserror%d%'


I tried the above query. The sps are retrieved
which has raiserror. But i need only the sps which has raiserror and %d in it.

Help me

Thanks in advance
Ismail
Posted
Updated 21-Oct-19 3:09am
v2
Comments
jerrykid 6-Jan-11 8:44am
   
good question, 5+
Rate this:
Please Sign up or sign in to vote.

Solution 1

This will do I hope.

SELECT * FROM (SELECT * FROM sp_Text WHERE obj_Text like '%raiserror%') result WHERE result.obj_Text LIKE '%d'


Edit:
Correct the typo in the statement.


Mark it as Answer if it helps you
   
v3
Comments
mugamath 6-Jan-11 5:41am
   
still nothing venkatesh.
jerrykid 6-Jan-11 8:37am
   
It should be SELECT * FROM (SELECT * FROM sp_Text WHERE obj_Text like '%raiserror%') result WHERE obj_Text LIKE '%d'
jerrykid 6-Jan-11 8:43am
   
good Venkatesh Mookkan, 5+
Venkatesh Mookkan 6-Jan-11 11:03am
   
@jerrykid thanks.

@mugamath:
I have corrected the SQL. It should work now.
mugamath 6-Jan-11 22:54pm
   
ventakesh this is the one you have given earlier. no differences.
Venkatesh Mookkan 6-Jan-11 22:56pm
   
Earlier result.obj_Text is misspelled as resul.obj_Text
mugamath 6-Jan-11 23:17pm
   
ya ok. but still couldn't get the answer.
Rate this:
Please Sign up or sign in to vote.

Solution 7

I've found all the answer now Let me try if I can help you.

There's called 'Escape character' in SQL Query matching pattern, Like If your character is matching with wildcard charcter then you can escape it.

Now the same problem arises for you that your character '%' is inside a string and you want to match it.

So there's lot of experiment in MSDN[^] reagarding.

Below para is of your solution on linked article.

You can search for character strings that include one or more of the special wildcard characters. For example, the discounts table in a customers database may store discount values that include a percent sign (%). To search for the percent sign as a character instead of as a wildcard character, the ESCAPE keyword and escape character must be provided. For example, a sample database contains a column named comment that contains the text 30%. To search for any rows that contain the string 30% anywhere in the comment column, specify a WHERE clause such as WHERE comment LIKE '%30!%%' ESCAPE '!'. If ESCAPE and the escape character are not specified, the Database Engine returns any rows with the string 30.
If there is no character after an escape character in the LIKE pattern, the pattern is not valid and the LIKE returns FALSE. If the character after an escape character is not a wildcard character, the escape character is discarded and the character following the escape is treated as a regular character in the pattern. This includes the percent sign (%), underscore (_), and left bracket ([) wildcard characters when they are enclosed in double brackets ([ ]). Also, within the double bracket characters ([ ]), escape characters can be used and the caret (^), hyphen (-), and right bracket (]) can be escaped.


Read full article over there you will surely get an answer.
   
v2
Comments
mugamath 6-Jan-11 23:08pm
   
its works fine for 30%. But i need %30 to be retrieved. i tried the below code
select * from (select * from sp_text where obj_Text like '%raiserror%')result
where obj_Text like '%d/%%'

Couldn't get the result
Rate this:
Please Sign up or sign in to vote.

Solution 5

Hello, If you are using SQL Server then see Pattern Matching in Search Conditions[^] look at Searching for Wildcard Characters
   
Rate this:
Please Sign up or sign in to vote.

Solution 3

How is this..,

TableName : CUSTOMER_MASTER
column name : FIRSTNAME

one row in my table has value "RA%JESH"

Now this is my Query

select * from CUSTOMER_MASTER cm where replace(cm.FIRSTNAME,'%','~') like '%~J%';


Added for Text datatype
select * from CUSTOMER_MASTER cm where replace(convert(varchar,cm.FIRSTNAME),'%','~') like '%~J%'


Let me know if it's helps you

Thanks & regards
Rajesh B
   
v2
Comments
mugamath 6-Jan-11 5:40am
   
thanks rajesh. Replace function doesn't accept the text data type as in my case obj_text is of data type text
Rajesh Anuhya 6-Jan-11 5:53am
   
change replace(cm.FIRSTNAME,'%','~') to replace(convert(varchar,cm.FIRSTNAME),'%','~')

here firstname is text datatype
mugamath 6-Jan-11 6:13am
   
sorry rajesh. still not. nothing is retrieved
Rajesh Anuhya 6-Jan-11 6:44am
   
it's working for me.., (with text datatype).., can you place your Query here..
mugamath 6-Jan-11 7:02am
   
need to retrieve the text which contains %d in it.

select * from sp_Text a where
replace(convert(varchar,a.obj_text),'%','~') like '%~s%'
Rajesh Anuhya 6-Jan-11 7:07am
   
select * from sp_Text a where replace(convert(varchar,a.obj_text),'%','~') like '%~d%'

you have to put 'd' instead of 's' ...
mugamath 6-Jan-11 7:28am
   
yes but see for instance. if i need %s , i shall use %s right. i have confirmed that %s is available in the text , but couldnt retrive the data.
Rate this:
Please Sign up or sign in to vote.

Solution 4

Hi Ismail:rose:

you try the following Code. it will be work well..

SELECT * FROM sp_text WHERE obj_Text LIKE '%'+@keyword +'%'
   
Comments
mugamath 6-Jan-11 6:18am
   
i tried this.
select * from sp_text where obj_Text like '%' + '%d' + '%'

but nothing had happened. actually in my text %d will be there. i need to retrieve all the text which has %d in it.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100