Solved

SQL SubString Query

Posted on 2006-11-17
8
2,292 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

830 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