?
Solved

How to find value in text

Posted on 2013-06-03
3
Medium Priority
?
318 Views
Last Modified: 2013-06-04
I am not sure which function to use to find the text I am looking for.  I thought it was the CHARINDEX but it is not returning the desired results.  I am looking for the name which starts in the same location every time but can be different lengths.  The end of the name is designated by a space.  I am using the following logic:

SUBSTRING(MASTER24_HISTORY.MRHISTORY,21,CHARINDEX(' ', LEFT(MASTER24_HISTORY.mrHISTORY,22)))

However it is pulling more than just the name.  Any idea why?
0
Comment
Question by:Gary Harper
[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 Comments
 
LVL 46

Accepted Solution

by:
Kent Olsen earned 1200 total points
ID: 39217553
Hi GHarper,

It looks like you're mixing techniques.  :)

First limit the variable (chop off the bit to the right:
  LEFT (item, charindex (item, ' ', start))

Then grab the desired portion:
  SUBSTRING (LEFT (item, charindex (item, ' ', start), start, limit)

Limit is the size of the variable.  You can compute a value, but this is easier to code.


Good Luck,
Kent
0
 
LVL 40

Expert Comment

by:lcohan
ID: 39219448
I suggest you look for PATINDEX ( '%pattern%' , expression ) to find the possition of the ' ' - or space char in your string and build/calculate your SUBSTRING based on that.
0
 

Author Comment

by:Gary Harper
ID: 39219538
kdo pointed me in the right direction.  I ended up using

SUBSTRING(item,21,CHARINDEX(' ',item,21) - 21) as 'Username'
0

Featured Post

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

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

762 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