Solved

Extracting an ID from a built up string?

Posted on 2008-10-02
2
273 Views
Last Modified: 2012-05-05
I am building up a string a storing them in the database:

eg:


72-8442

I was wondering how I would go about extracting '8442' from this string in SQL because I would like to do:

select serialnumber from assets where id = '8442'

Any ideas?

The Format is always:

ID1-ID2
72-8442

Cheers
0
Comment
Question by:directxBOB
[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
2 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 250 total points
ID: 22624072
if id1 is always 2 characters then....

select serialnumber from assets where substr(id,4) = '8442'

if id1 might vary in length then...

select serialnumber from assets where substr(id,instr(id,'-')+1) = '8442'


note, you won't be able to use an index on id when applying these conditions unless you create a function based index
0
 
LVL 4

Assisted Solution

by:GO-87
GO-87 earned 250 total points
ID: 22624128
Or if you don't know what the number will be, and assuming the column you're selecting is called myID:


select substring(myID, charindex('-',myID,1)+1, len(myID) ) 

Open in new window

0

Featured Post

Want Experts Exchange at your fingertips?

With Experts Exchange’s latest app release, you can now experience our most recent features, updates, and the same community interface while on-the-go. Download our latest app release at the Android or Apple stores today!

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
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…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Suggested Courses

626 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