Thanks! I used a combination of yours and Isaac's. I know have DTS setup to
do everything and it works great. I'm still new to sql server so I still
stumble around more than I would like to admit.
Phillip Broussard
Tracker Marine Group
417-873-5957
----- Original Message -----
From: "Van Vliet, Scott" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Monday, July 29, 2002 4:33 PM
Subject: RE: SQL question
> 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
