Two basic questions on how to use SUBSTRING

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Two basic questions on how to use SUBSTRING

Author

Topic

rwsjbs
Starting Member

17 Posts

Posted - 2010-04-14 : 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
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-15 : 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.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-15 : 09:10:34

Also read about parsename function

Madhivanan

Failing to plan is Planning to fail

rwsjbs
Starting Member

17 Posts

Posted - 2010-04-16 : 11:19:42

Thanks so much for the query. I really appreciate you posting it. That saved me much time.

Thanks,
Richard Scott

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-18 : 13:00:23

welcome

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

Kristen
Test

22859 Posts

Posted - 2010-04-18 : 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
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-18 : 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

22859 Posts

Posted - 2010-04-18 : 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

38200 Posts

Posted - 2010-04-18 : 16:05:27

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

Kristen
Test

22859 Posts

Posted - 2010-04-19 : 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

22864 Posts

Posted - 2010-04-19 : 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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources