SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Two basic questions on how to use SUBSTRING
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rwsjbs
Starting Member

17 Posts

Posted - 04/14/2010 :  19:15:56  Show Profile  Reply with Quote
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
8765 Posts

Posted - 04/15/2010 :  01:42:05  Show Profile  Visit webfred's Homepage  Reply with Quote
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
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 04/15/2010 :  09:10:34  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Also read about parsename function

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

rwsjbs
Starting Member

17 Posts

Posted - 04/16/2010 :  11:19:42  Show Profile  Reply with Quote
Thanks so much for the query. I really appreciate you posting it. That saved me much time.

Thanks,
Richard Scott
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8765 Posts

Posted - 04/18/2010 :  13:00:23  Show Profile  Visit webfred's Homepage  Reply with Quote
welcome


No, you're never too old to Yak'n'Roll if you're too young to die.
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 04/18/2010 :  14:09:53  Show Profile  Reply with Quote
" 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 ...
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8765 Posts

Posted - 04/18/2010 :  14:15:02  Show Profile  Visit webfred's Homepage  Reply with Quote
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.
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 04/18/2010 :  15:51:29  Show Profile  Reply with Quote
Me?

INTEGER part (to give you the "3" part), and MODULOUS or REMAINDER (to give you the ".95" part).

How did I do? !!
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37129 Posts

Posted - 04/18/2010 :  16:05:27  Show Profile  Visit tkizer's Homepage  Reply with Quote
Use the FLOOR function to get the whole integer. To get the right side of the decimal place, subtract off FLOOR, and then multiply by 100.

SELECT FLOOR(3.95)
SELECT (3.95 - FLOOR(3.95)) * 100

I don't have SQL Server in front of me to test though, so please test on your own.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 04/19/2010 :  03:00:38  Show Profile  Reply with Quote
please test on your own.

Check!

SELECT FLOOR(3.95)
SELECT (3.95 - FLOOR(3.95)) * 100
      
----- 
3

------------ 
95.00

Check!!
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 04/19/2010 :  05:47:13  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000