Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

How to find value in text

Posted on 2013-06-03
3
Medium Priority
?
320 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 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…

564 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