Solved

SQL SubString Query

Posted on 2006-11-17
8
2,296 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
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 

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

Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

Question has a verified solution.

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

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…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
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…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

635 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