Right Trim

[klaidlaw]

I am SQL Server 2008

I can't figure out how to trim my characters after a decimal has occured. For example I have 123.56 and I just want 123. Then problem is the strings are not always the same length, because I wanted to just use substring but I am not sure how to write where to make the function end. If anyone has any tips I would appreciate it.

Thanks

 

[gmmastros]

What is the data type you are dealing with? If it's a number type, you should use a rounding method. If it's a string, then you could use the parsename function.

ex:

Select ParseName('123.56', 2), Parsename('123.56',1)

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[klaidlaw]

It is a varchar but I figured it out I was about to post:
SUBSTRING(columnname, 1, PATINDEX('%.%',columnname))

 

[gmmastros]

There is a slight *potential* problem with that code. If your varchar data does not contain a dot, you won't get anything. If it does contain a dot, you'll also get the dot. This is probably not what you want. The parsename code I presented earlier has the same problem.

Slightly better would be:

SUBSTRING(columnname, 1, PATINDEX('%.%',columnname[!] + '.'[/!])[!]-1[/!])

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[TheBugSlayer]

DECLARE @WholeNumber VARCHAR(10)
SELECT @WholeNumber = '12356'
--SELECT @WholeNumber = '123.56'
SELECT 
  WholeNumber = CASE 
                  WHEN CHARINDEX('.', @WholeNumber) <= 0 THEN @WholeNumber
                  ELSE SUBSTRING(@WholeNumber, 0, CHARINDEX('.', @WholeNumber))
                END

Good luck.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[ca8msm]

As George pointed out, you may be able to use a rounding function depending on what data you have stored, for example try it out on your dataset just using the floor method:

select floor('123.56')

Also I think this method will convert the data type to a float so you may need to bear this in mind if you are performing any calculations on the data.



Mark,

Darlington Web Design[tab]|[tab]Experts, Information, Ideas & Knowledge[tab]|[tab]ASP.NET Tips & Tricks