| Author |
 Topic  |
|
|
rwsjbs
Starting Member
17 Posts |
 Posted - 04/14/2010 : 19:15:56
|
Thank you for looking at my post.
I have two questions:
Question 1 - I need to use the SUBSTRING command to return the 3 in a field that is currently 3.95
So, if the field is 3.95
I only want to return the 3
Question 2 - I need the SUBSTRING command to identiry the .95 in a field that is 3.95
So, if the field is 3.95
I only want to return the 95
For both questions there is always a period (.) that seperates the field. The field is actually a price field. So I am trying to seperate the dollars from the cents.
Any advise you could give would help this novice.
Thank you,
Richard Scott
|
|
|
webfred
Flowing Fount of Yak Knowledge
Germany
8514 Posts |
Posted - 04/15/2010 : 01:42:05
|
If you want to use SUBSTRING for this then you can do it like this:
declare @test money
set @test=3.95
select @test
SELECT SUBSTRING(CAST(@test AS VARCHAR(10)),1,CHARINDEX('.',CAST(@test AS VARCHAR(10)))-1)
SELECT SUBSTRING(CAST(@test AS VARCHAR(10)),CHARINDEX('.',CAST(@test AS VARCHAR(10)))+1,LEN(CAST(@test AS VARCHAR(10))))
Or you google "sql server get the parts of decimal value" to get many other ideas...
edit: typo
No, you're never too old to Yak'n'Roll if you're too young to die. |
Edited by - webfred on 04/15/2010 01:42:35 |
 |
|
|
madhivanan
Premature Yak Congratulator
India
22461 Posts |
Posted - 04/15/2010 : 09:10:34
|
Also read about parsename function
Madhivanan
Failing to plan is Planning to fail |
|
|
|
rwsjbs
Starting Member
17 Posts |
Posted - 04/16/2010 : 11:19:42
|
Thanks so much for the query. I really appreciate you posting it. That saved me much time.
Thanks,
Richard Scott |
|
|
|
webfred
Flowing Fount of Yak Knowledge
Germany
8514 Posts |
Posted - 04/18/2010 : 13:00:23
|
welcome 
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 04/18/2010 : 14:09:53
|
" The field is actually a price field"
In that case I think it would be better to do it arithmetically, rather than using String functions ... |
|
|
|
webfred
Flowing Fount of Yak Knowledge
Germany
8514 Posts |
Posted - 04/18/2010 : 14:15:02
|
quote:
Originally posted by Kristen
" The field is actually a price field"
In that case I think it would be better to do it arithmetically, rather than using String functions ...
Yes.
Can you show a solution?
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 04/18/2010 : 15:51:29
|
Me? 
INTEGER part (to give you the "3" part), and MODULOUS or REMAINDER (to give you the ".95" part).
How did I do? !! |
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 04/19/2010 : 03:00:38
|
please test on your own.
Check!
SELECT FLOOR(3.95)
SELECT (3.95 - FLOOR(3.95)) * 100
-----
3
------------
95.00
Check!! |
|
|
|
madhivanan
Premature Yak Congratulator
India
22461 Posts |
Posted - 04/19/2010 : 05:47:13
|
If there are only two decimal places
SELECT FLOOR(3.95),FLOOR(3.95*100%100)
or
SELECT cast(3.95 as int),cast(3.95*100%100 as int)
Madhivanan
Failing to plan is Planning to fail |
|
|
| |
Topic |
|
Two basic questions on how to use SUBSTRING
