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
Solved

Extracting an ID from a built up string?

Posted on 2008-10-02
2
260 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
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone 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

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

809 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