Why don't you just use Transact-SQL functions?
SELECT SUBSTRING(COL_NAME,1,(PATINDEX('%.%',COL_NAME)-1))
FROM TABLE_NAME
This will return any characters before the '.'. NOTE that the STR()
function will also do this for you with columns of type FLOAT.
ALSO NOTE that it would be smart to validate this function to make sure that
the PATINDEX() doesn't return 0 (resulting in an error);
SELECT
CASE
WHEN (PATINDEX('%.%',COL_NAME) > 0 THEN
SUBSTRING(COL_NAME,1,(PATINDEX('%.%',COL_NAME)-1))
ELSE
COL_NAME
END
FROM TABLE_NAME
Hope this helps!
--
Scott Van Vliet
Sempra Energy
555 W. 5th St., 21st Floor
Los Angeles, CA 90013
Tel > 213.244.5205
Email > [EMAIL PROTECTED]
"Hello Stupid, and welcome to your crappy computer."
- Strong Bad, HomestarRunner.com
> -----Original Message-----
> From: S. Isaac Dealey [mailto:[EMAIL PROTECTED]]
> Sent: Monday, July 29, 2002 2:07 PM
> To: CF-Talk
> Subject: RE: SQL question
>
>
> > -----Original Message-----
> > From: Phillip B [mailto:[EMAIL PROTECTED]]
> > Sent: Monday, July 29, 2002 1:06 PM
> > To: CF-Talk
> > Subject: OT: SQL question
>
> <snippage>
>
> > The part before the dot is all I want to use to compare to
> the other table
> > which will only contain the first part. It would be
> something like this.
> >
> > 12345.123 = 12345
> > 12345.234 = 12345
> > 12345.654 = 12345
> > 02nt911dc.123 = 02nt911dc
> > 02nt911dc.054 = 02nt911dc
> > 02thq5 = 02thq5
> > 02thq7 = 02thq7
> >
> > How would I do that using just sql statements?
>
> I would imagine you could use a like statement ...
>
> WHERE '12345.123' LIKE '12345' + '.%'
>
> or rather
>
> WHERE table1.partnumber LIKE table2.partnumber + '.%'
>
> Isaac Dealey
> Certified Advanced ColdFusion Developer
>
> www.turnkey.to
> 954-776-0046
>
______________________________________________________________________
This list and all House of Fusion resources hosted by CFHosting.com. The place for
dependable ColdFusion Hosting.
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
