Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL SubString Query

Posted on 2006-11-17
8
Medium Priority
?
2,301 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 

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 1000 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 1000 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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

721 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