Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Triming Data

Status
Not open for further replies.

JezEling

IS-IT--Management
Mar 22, 2004
127
GB
Hi all,

Having done lots of searching on the net and trying out of SQL this problem has driven me mad, I need to strip certain pieces of information from a value held in a field in SQL in order to perform a calcuation on the remaining value.

E.G.

The original value - 024967626936
What I need - 496762693

Does anyone have any ideas how I can go about doing this?

Many thanks in advance

Jez
 
What are the rules for which info you want to extract? From your example it could be "take off the first two digits and the last digit" or it could be "take 9 digits starting from the third digit".

--James
 
The rule is take off the first two and the last one.
 
Try this then:

Code:
SELECT SUBSTRING(col, 3, LEN(col) - 3)

--James
 
If the value is expected to be of the same size then try this:
Code:
select substring('024967626936', 3, len('024967626936') - 3)
Else something like this:
Code:
select substring(substring('024967626936', 3, len('024967626936')), 1, len(substring('024967626936', 3, len('024967626936'))) - 1)

Regards,
AA


 
That worked a treat. Just to complicate matters a little bit, would it be possible to strip the first two numbers off the value and put them in a seperate field?

Thanks for your help on this

Jez
 
Code:
SELECT SUBSTRING(col, 3, LEN(col) - 3), SUBSTRING(col,1,2)
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top