Triming Data

[JezEling]

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

 

[JamesLean]

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

 

[JezEling]

The rule is take off the first two and the last one.

 

[JamesLean]

Try this then:

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

--James

 

[amrita418]

If the value is expected to be of the same size then try this:

select substring('024967626936', 3, len('024967626936') - 3)

Else something like this:

select substring(substring('024967626936', 3, len('024967626936')), 1, len(substring('024967626936', 3, len('024967626936'))) - 1)

Regards,
AA

 

[JezEling]

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

 

[Juice05]

SELECT SUBSTRING(col, 3, LEN(col) - 3), SUBSTRING(col,1,2)