Solved

SQL SubString Query

Posted on 2006-11-17
8
2,293 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Business Impact of IT Communications

What are the business impacts of how well businesses communicate during an IT incident? Targeting, speed, and transparency all matter. Find out more in this infographic.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
how to resize column length with primary ket 4 34
Moving away from Access 2003 adp files 4 48
SQL query 45 41
Importing from CSV to SSMS 2 6
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…
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…
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 shrink a transaction log file down to a reasonable size.

734 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