Alternative to LTRIM?

Question

Post reply

Alternative to LTRIM?

edhunkin

SSC Veteran

Points: 252
More actions
March 11, 2002 at 6:31 am

#158172

I have a CHAR field with thousands of rows of data that looks like this:
QuestionNumber
--------------
2.1
2.2
2.2.1
4.16
6.0
6.0.1
6.0.2
etc. etc. Anyhow, I wanted to do some kind of stripping of all the data to the right of the first decimal place -- some way to get this result set:
QuestionNumber
--------------
2
2
2
4
6
6
6
I cannot do a simple LTRIM because sometimes the numbers to the left of the first "." are a single digit, and sometimes two or three digits.
Anyone have any ideas?
Ed Hunkin

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Clive Strong SSChampion Points: 11222 More actions · Answer 1

Hey Ed,

This is possible. A simple syntax would be.

SELECT LEFT(QuestionNumber, CHARINDEX('.', QuestionNumber, 1)-1) As [QuestionNumber]FROM <TableName>

Clive

Clive Strong

clive.strong@btinternet.com

edhunkin SSC Veteran Points: 252 More actions · Answer 2

Thanks Clive, that's a good one. Now my only problem is that I have to do the same thing with a "portable" version of the DB that's on Access2K.

And Access2000 will not let you use CHARINDEX.

SQL I know fairly well. Access I don't know (or like) much at all. Anyone have any ideas?

thanks,

Ed

ps don't hate me because I said the dreaded Access word. It's not my choice.

Edited by - edhunkin on 03/11/2002 11:34:12 AM

Andy Warren SSC Guru Points: 119902 More actions · Answer 3

One way would be create your own function, maybe something like this:

Public Function NoDecimal(DataIn As String) As String

Dim J As Integer

Dim x As Integer

'default

x = 1

For J = 1 To Len(DataIn)

If Mid$(DataIn, J, 1) = "." Then

x = J + 1

Exit For

End If

End Function

Other options would be to create a view that does the work server side, then you don't have to do it in Access. You could also explore using pass through queries in Access that let you bypass Jet.

Andy

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter

Antares686 SSC Guru Points: 125444 More actions · Answer 4

For Access it is InStr function that returns the position of the first occurance of an item.

SELECT LEFT(QuestionNumber,INSTR(QuestionNumber,'.') - 1) AS Expr1 FROM tblX

"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

edhunkin SSC Veteran Points: 252 More actions · Answer 5

Thanks Andy, now I owe you and Steve Jones both a beer.

All three sound like very good ideas. The one that I had started experimenting with already is the view from the SQL-side. Before I settle on that 100% though, I will try your other idea using that function.

thanks very much,

Ed

edhunkin SSC Veteran Points: 252 More actions · Answer 6

Spoke too soon apparently, as the INSTR works great. I'm sure that I'll be using that in this dreaded Access.

The SQL parts are fun.

(I just keep saying that to myself.)

Thanks very much Antares.

Ed