Solved

SQL SubString Query

Posted on 2006-11-17
8
2,291 Views
Last Modified: 2012-05-05
Hi I have the following text in a column of text type.

I am trying to extract the  SPROC name from the following string  

“exec risp_GetBusinessUnitSupportEmail 100488”  


I have got to this point so far. Where I do a substring on the first occurrence of the characters “risp”. However I don’t know how to find the end of the sproc name i.e. the empty string without making it too complex. Can someone help in simplifying it. Thanks


select      top 1      convert(varchar(100),TextData),
 substring (   convert(varchar(100),TextData),  charindex( 'risp' , convert(varchar(100),TextData)  ), 1   )
 from ritrace (nolock) where   textdata is not
0
Comment
Question by:anwarmir
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 39

Expert Comment

by:appari
ID: 17964046
select 'exec risp_GetBusinessUnitSupportEmail 100488',
substring ('exec risp_GetBusinessUnitSupportEmail 100488',
charindex(' ','exec risp_GetBusinessUnitSupportEmail 100488') + 1,
charindex(' ','exec risp_GetBusinessUnitSupportEmail 100488',
charindex(' ','exec risp_GetBusinessUnitSupportEmail 100488') + 1) - charindex(' ','exec risp_GetBusinessUnitSupportEmail 100488') )
0
 
LVL 39

Expert Comment

by:appari
ID: 17964051

the above SQL assumes
1. only one space separates the words.
2. atleast two spaces exists in the string
0
 
LVL 6

Expert Comment

by:wtapsell
ID: 17964171
This should work but has one problem.  If the stroed procedure ends in 'exec ' the exec will be replaced with a space:

declare @x varchar(2000)

set @x = 'exec     risp_GetBusinessUnitSupportEmail     100488'

select left(ltrim(replace(@x, 'exec ', '')), patindex('% %', ltrim(replace(@x, 'exec ', ''))) - 1 )

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:anwarmir
ID: 17964573
This was my  solution:


select  substring (convert(varchar(1000),TextData),   charindex('risp', convert(varchar(100),TextData)),  
charindex(' ', convert(varchar(1000),TextData),    charindex('risp', convert(varchar(100),TextData)) )                  
  )      from ritrace


but still does not work

it falls over when no params are passed to the Sproce i.e. " exec risp_GetCampaignSearchEngines". I get Invalid length parameter passed to the substring function.

Appari and wtaSpell. I get the same error message with both solutions provided.





0
 

Author Comment

by:anwarmir
ID: 17964700
OK I got round it by adding a blank string like this convert(varchar(2000),TextData)+ ' ', so to ensure that text data alawys has a blank string.

But still having problems on the end position of the substring , I'm getting risp_GetCampaignPageName 10048 for string like this


exec risp_GetCampaignPageName 100488, 0, 0  
0
 
LVL 32

Expert Comment

by:awking00
ID: 17964975
In these two strings, what is the sproc name you expect to retrieve?
exec risp_GetBusinessUnitSupportEmail 100488
exec risp_GetCampaignPageName 100488, 0, 0
0
 
LVL 6

Accepted Solution

by:
wtapsell earned 250 total points
ID: 17965029
You need to take away the length to the risp_ value.  I have adjusted your query with - charindex part at the end:

select  substring (convert(varchar(1000),TextData),   charindex('risp', convert(varchar(100),TextData)),  
charindex(' ', convert(varchar(1000),TextData),    charindex('risp', convert(varchar(100),TextData)) )  - charindex('risp', convert(varchar(100),TextData)))                
  )      from ritrace
0
 
LVL 39

Assisted Solution

by:appari
appari earned 250 total points
ID: 17965055
try this

select
substring (convert(varchar(100),TextData) + ' '  ,
charindex(' ',convert(varchar(100),TextData) + ' ' ) + 1,
charindex(' ',convert(varchar(100),TextData) + ' ' ,
charindex(' ',convert(varchar(100),TextData) + ' ' ) + 1) - charindex(' ',convert(varchar(100),TextData) + ' ' ) )

0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question