SQL question

[ladyskydiver 0]

Ok...my brain is non-functional today - no smartass comments, Remi.

Using SQL, I need to get the left of a derived field. For example,

DerivedField ----------------> DesiredResult

39.74834738 ----------------> 39
9.44 ----------------> 9
137102837.223 --------------> 137102837

HELP!!!

Life is short! Break the rules! Forgive quickly! Kiss slowly! Love truly, Laugh uncontrollably. And never regret anything that made you smile.

[pilotdave 0]

Select truncate... will probably do what you want. Is that a standard SQL function?

select truncate(1.999,0) will return 1.

Dave

[ianmdrennan 2]

SELECT LEFT(DerivedField , CHARINDEX('.', DerivedField ) -1)

This is T-SQL btw.

Performance Designs Factory Team

[ladyskydiver 0]

Thanks!!!! It works!

Life is short! Break the rules! Forgive quickly! Kiss slowly! Love truly, Laugh uncontrollably. And never regret anything that made you smile.

[ianmdrennan 2]

which one?

Performance Designs Factory Team

[ladyskydiver 0]

Yours. Kept getting an error with the truncate although it's a valid SQL command.

Life is short! Break the rules! Forgive quickly! Kiss slowly! Love truly, Laugh uncontrollably. And never regret anything that made you smile.

[ianmdrennan 2]

Sweet - glad to help.

Blues,
Ian
aka Your Friendly Neighborhood SQL dork.

Performance Designs Factory Team

[ladyskydiver 0]

Well...the neighborhood friendly SQL dork is owed a hug by me. Thanks!

Life is short! Break the rules! Forgive quickly! Kiss slowly! Love truly, Laugh uncontrollably. And never regret anything that made you smile.

[metalslug 36]

These are numbers, therefore not entirely correct to treat them as character strings.
The correct mathematical function is FLOOR

SELECT FLOOR (39.74834738), FLOOR (9.44), FLOOR(137102837.223)




-

No 'mericans were harmed during the making of this post.

[ianmdrennan 2]

Only if you want it to perform the mathematical function. If she explicitly wants the left without any conversions (such as FLOOR(-123.45) returning -124 instead of -123) basically, it just depends what she considers the "correct" result.

Ladyskydiver, make sure you confirm which one is most appropriate for your expected data.

edit:

Quote

These are numbers, therefore not entirely correct to treat them as character strings.

Generally yep that's a good point.


Another Edit: Copied and pasted from the help file for ladyskydiver to help her make her decision:

FLOOR
Returns the largest integer less than or equal to the given numeric expression.

Syntax
FLOOR ( numeric_expression )

Arguments
numeric_expression

Is an expression of the exact numeric or approximate numeric data type category, except for the bit data type.

Return Types
Returns the same type as numeric_expression.

Examples
This example shows positive numeric, negative numeric, and currency values with the FLOOR function.

SELECT FLOOR(123.45), FLOOR(-123.45), FLOOR($123.45)

The result is the integer portion of the calculated value in the same data type as numeric_expression.

--------- --------- -----------
123 -124 123.0000

Blues,
Ian

ps: raise your hand if you've been bitten in the ass before thanks to Bankers Rounding!

Performance Designs Factory Team

[pilotdave 0]

What was wrong with truncate? Seemed a lot easier.

Dave

[ianmdrennan 2]

not a supported T-SQL word (in that context) I believe

You can use it on tables though.

Performance Designs Factory Team