SQL Code question | Forum

Topic location: Forum home » Reporting » DR Reporting
Dec 5 '14
2014-12-05 10:30:40

Hi all,
We are a 6.07 site,
I am trying to basically get part of a string:
This is the starting string:
Ex. G0-20120402180416698-1...the issu
I want the characters in yellow.

SUBSTRING(OrderId,11,len(RX.OrderId)-2)
I cannot get the len function to work in the substring function. My thinking is that the len function should return a number. That should statisfy the 3rd requirement if the substring function.
What happens is it just selects all the "OrderId" characters past the 11th postion.(this includes the -1...which I am ultimately trying to get rid of).
Any thoughts would be great!
Thanks,
Mark
PRIVACY NOTICE: This e-mail message, including any attachments, is
for the sole use of the intended recipient(s) and the information is
confidential, privileged or protected by law. Any unauthorized review,
use, disclosure, copying, or distribution to anyone other than the specific
person or office addressed is strictly prohibited. If you are not the
intended recipient, please contact the sender by reply e-mail and
destroy all copies of the original message and attachments.
Dec 5 '14
2014-12-05 11:06:14
Mark...
select SUBSTRING('G0-20120402180416698-1',11,LEN('G0-20120402180416698-1')-13)
OR
select SUBSTRING(RX.OrderId,11,LEN(RX.OrderId)-13)
Thanks,
Kevin
Dec 5 '14
2014-12-05 11:21:50
Try something like this Mark...

declare @temp varchar(30)
set @temp = 'G0-20120402180416698-1'
print substring(@temp, 11, len(@temp) - 12)

Thanks,
John Taylor
Systems Analyst, Information Technology
Augusta Health | Office: (540)332-4882
Dec 5 '14
2014-12-05 12:41:49
It the string you are looking for always starts in position 11 then
SUBSTRING(OrderId,11,11) Start at position 11 and get 11 characters.

Jorge
SQLPower4DR
[cid:image001.png@01D010A1.EE4F0700]

Dec 5 '14
2014-12-05 12:52:11
Hi Mark,

As you point out, the error occurs because a negative number is not a valid
input parameter to the SQL Server LEN() function. By seeding the second
parameter of the SUBSTRING function with the value of 11, there is an
implicit assumption that the OrderId field is at least 11 characters long.
The easiest way to avoid the SQL error is to use a CASE statement and a
WHERE clause (see below). Alternatively, you could write a query that
identifies the cases where the OrderId is less than 11 characters, review
the output, figure out why these cases exist, and adjust the code
accordingly (if you think the cases do represent valid data).

SELECT
CASE
WHEN LEN(RX.OrderId) > 11 THEN SUBSTRING(RX.OrderId, 11,
LEN(RX.OrderId) - 2)
ELSE NULL - Prevents SQL from throwing an error by attempting to
pass a negative number to the LEN function
END
FROM PhaRx AS RX
WHERE
LEN(RX.OrderId) > 11 -- Filters bogus OrderIds from results


Hope this is helpful.

Glen


<http://www.acmeware.com/>; cid:image001.png@01CFE476.61FB0B70
Data Repository Experts Since 1998



Maxxx
Maxxx Jun 11
audiobookkeeper.rucottagenet.rueyesvision.rueyesvisions.comfactoringfee.rufilmzones.rugadwall.rugaffertape.rugageboard.rugagrule.rugallduct.rugalvanometric.rugangforeman.rugangwayplatform.rugarbagechute.rugardeningleave.rugascautery.rugashbucket.rugasreturn.rugatedsweep.rugaugemodel.rugaussianfilter.rugearpitchdiameter.rugeartreating.ru generalizedanalysis.rugeneralprovisions.rugeophysicalprobe.rugeriatricnurse.rugetintoaflap.rugetthebounce.ruhabeascorpus.ruhabituate.ruhackedbolt.ruhackworker.ruhadronicannihilation.ruhaemagglutinin.ruhailsquall.ruhairysphere.ruhalforderfringe.ruhalfsiblings.ruhallofresidence.ruhaltstate.ruhandcoding.ruhandportedhead.ruhandradar.ruhandsfreetelephone.ruhangonpart.ruhaphazardwinding.ru hardalloyteeth.ruhardasiron.ruhardenedconcrete.ruharmonicinteraction.ruhartlaubgoose.ruhatchholddown.ruhaveafinetime.ruhazardousatmosphere.ruheadregulator.ruheartofgold.ruheatageingresistance.ruheatinggas.ruheavydutymetalcutting.rujacketedwall.rujapanesecedar.rujibtypecrane.rujobabandonment.rujobstress.rujogformation.rujointcapsule.rujointsealingmaterial.rujournallubricator.rujuicecatcher.rujunctionofchannels.ru justiciablehomicide.rujuxtapositiontwin.rukaposidisease.rukeepagoodoffing.rukeepsmthinhand.rukentishglory.rukerbweight.rukerrrotation.rukeymanassurance.rukeyserum.rukickplate.rukillthefattedcalf.rukilowattsecond.rukingweakfish.rukinozones.rukleinbottle.rukneejoint.ruknifesethouse.ruknockonatom.ruknowledgestate.rukondoferromagnet.rulabeledgraph.rulaborracket.rulabourearnings.ru labourleasing.rulaburnumtree.rulacingcourse.rulacrimalpoint.rulactogenicfactor.rulacunarycoefficient.ruladletreatediron.rulaggingload.rulaissezaller.rulambdatransition.rulaminatedmaterial.rulammasshoot.rulamphouse.rulancecorporal.rulancingdie.rulandingdoor.rulandmarksensor.rulandreform.rulanduseratio.rulanguagelaboratory.rulargeheart.rulasercalibration.rulaserlens.rulaserpulse.ru laterevent.rulatrinesergeant.rulayabout.ruleadcoating.ruleadingfirm.rulearningcurve.ruleaveword.rumachinesensible.rumagneticequator.rumagnetotelluricfield.rumailinghouse.rumajorconcern.rumammasdarling.rumanagerialstaff.rumanipulatinghand.rumanualchoke.rumedinfobooks.rump3lists.runameresolution.runaphtheneseries.runarrowmouthed.runationalcensus.runaturalfunctor.runavelseed.ru neatplaster.runecroticcaries.runegativefibration.runeighbouringrights.ruobjectmodule.ruobservationballoon.ruobstructivepatent.ruoceanmining.ruoctupolephonon.ruofflinesystem.ruoffsetholder.ruolibanumresinoid.ruonesticket.rupackedspheres.rupagingterminal.rupalatinebones.rupalmberry.rupapercoating.ruparaconvexgroup.ruparasolmonoplane.ruparkingbrake.rupartfamily.rupartialmajorant.ruquadrupleworm.ru qualitybooster.ruquasimoney.ruquenchedspark.ruquodrecuperet.rurabbetledge.ruradialchaser.ruradiationestimator.rurailwaybridge.rurandomcoloration.rurapidgrowth.rurattlesnakemaster.rureachthroughregion.rureadingmagnifier.rurearchain.rurecessioncone.rurecordedassignment.rurectifiersubstation.ruredemptionvalue.rureducingflange.rureferenceantigen.ruregeneratedprotein.rureinvestmentplan.rusafedrilling.rusagprofile.ru salestypelease.rusamplinginterval.rusatellitehydrology.ruscarcecommodity.ruscrapermat.ruscrewingunit.ruseawaterpump.rusecondaryblock.rusecularclergy.ruseismicefficiency.ruselectivediffuser.rusemiasphalticflux.rusemifinishmachining.ruspicetrade.ruspysale.rustungun.rutacticaldiameter.rutailstockcenter.rutamecurve.rutapecorrection.rutappingchuck.rutaskreasoning.rutechnicalgrade.rutelangiectaticlipoma.ru telescopicdamper.rutemperateclimate.rutemperedmeasure.rutenementbuilding.rutuchkasultramaficrock.ruultraviolettesting.ru