Solved

How to find value in text

Posted on 2013-06-03
3
302 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
3 Comments
 
LVL 45

Accepted Solution

by:
Kdo earned 400 total points
Comment Utility
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 39

Expert Comment

by:lcohan
Comment Utility
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
Comment Utility
kdo pointed me in the right direction.  I ended up using

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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
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…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

763 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now