To search for a string in SP?

Question

Post reply

To search for a string in SP?

rajagopalanseeth

SSC Enthusiast

Points: 152
More actions
September 21, 2009 at 8:26 am

#138987

Hi,
I want to search for a string in Stored Procedure. I am new to SQL. What should i do?.
---Boundary-00=_BLD1cvEPKmuNkgPvUNY3 Content-Type: text/plain; charset=utf-8; name=footer.txt Content-Disposition: attachment; filename="footer.txt" Content-Transfer-Encoding: base64 G:\Test\Attach\footer.txt --------------Boundary-00=_BLD1cvEPKmuNkgPvUNY3-- ------_=_NextPart_002_01C84D7E.9133E008-- ------_=_NextPart_001_01C84D7E.9133E008 Content-Type: application/vnd.ms-excel; name="Press.xls" Content-Description: Press list for partners.xls Content-Disposition: attachment; filename="Press.xls" Content-Transfer-Encoding: base64 g:\test\Attach\Deb\Press.xls ------_=_NextPart_001_01C84D7E.9133E008--
The above is a string i will be getting from a table. I want to get only the value "G:\Test\Attach\footer.txt". How to go about it.. Plese help me out?
Thanks
Raja

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

dsdeming Ten Centuries Points: 1236 More actions · Answer 1

You can search the syscomments table: SELECT DISTINCT OBJECT_NAME(id) FROM syscomments WHERE text LIKE '%YourTextHere%'

Mark Cowne One Orange Chip Points: 26865 More actions · Answer 2

SELECT name FROM sys.objects WHERE type='P' AND OBJECT_DEFINITION(OBJECT_ID) LIKE '%sometext%'

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

rajagopalanseeth SSC Enthusiast Points: 152 More actions · Answer 3

Hi,

I am not sure that i understood what you have written. Can you please explain more about it?. I think i would like to explain more about what i do. I am migrating from One database to another. In the Old database, in a table i have a column that contains the above string(that's a part of the string). I have to get the filepath from that string and then insert it in a new table for the New Databse . I will be doing it in the database.

Thanks,

Raja.

dsdeming Ten Centuries Points: 1236 More actions · Answer 4

If I understand you correctly, you just want to extract one string from another. For that you need to use SQL's string manipulation functions, such as they are. Assuming that the string you want to retrieve is always going to be follow 'base64' and will not contain embedded spaces:

declare @a varchar(1000)

set @a = '---Boundary-00=_BLD1cvEPKmuNkgPvUNY3 Content-Type: text/plain; charset=utf-8; name=footer.txt Content-Disposition: attachment; filename="footer.txt" Content-Transfer-Encoding: base64 G:\Test\Attach\footer.txt --------------Boundary-00=_BLD1cvEPKmuNkgPvUNY3-- ------_=_NextPart_002_01C84D7E.9133E008-- ------_=_NextPart_001_01C84D7E.9133E008 Content-Type: application/vnd.ms-excel; name="Press.xls" Content-Description: Press list for partners.xls Content-Disposition: attachment; filename="Press.xls" Content-Transfer-Encoding: base64 g:\test\Attach\Deb\Press.xls ------_=_NextPart_001_01C84D7E.9133E008--'

select LEFT(LTRIM(SUBSTRING(@a, CHARINDEX( 'base64', @a) + 7, 100 )), CHARINDEX( ' ', LTRIM(substring(@a, CHARINDEX( 'base64', @a) + 7, 100 ))))