• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 283
  • Last Modified:

Extracting an ID from a built up string?

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
directxBOB
Asked:
directxBOB
2 Solutions
 
sdstuberCommented:
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
 
GO-87Commented:
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

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now