December 6, 2007 at 5:52 pm
I am creating a view to pull data for a UPS integration I am doing. I currently have this view where I pull my data from. All is well accept for my PATINDEX,
Currently I have this PATINDEX So when a user puts this into delivery instructions "#999999" UPS_FINAL returns 999999 which is good for me to use. But The PATINDEX will also grab all kinds of neat stuff out of the delivery instructions field when I really only want whatever is after the # sign in the field.
I am new to sql and dont quite understand how this search is working but I think I am in need of a better way to search the field
WHEN SUBSTRING(P21PLAY.dbo.p21_view_oe_hdr.delivery_instructions, PATINDEX('%[^a-z ]%', P21PLAY.dbo.p21_view_oe_hdr.delivery_instructions) + 1, 6) IS NULL
THEN dbo.Address_Table.ups_code
ELSE SUBSTRING(P21PLAY.dbo.p21_view_oe_hdr.delivery_instructions,
PATINDEX('%[^a-z ]%', P21PLAY.dbo.p21_view_oe_hdr.delivery_instructions) + 1, 6) END AS UPS_FINAL
-------------------------------------------------------------------------------------
What I am looking to accomplish is a SUBSTRING that will search delivery_instructions
SELECT
TOP (100) PERCENT P21PLAY.dbo.p21_view_oe_pick_ticket.pick_ticket_no,
P21PLAY.dbo.p21_view_oe_hdr.order_no, P21PLAY.dbo.p21_view_oe_hdr.customer_id,
P21PLAY.dbo.p21_view_oe_hdr.ship2_name, P21PLAY.dbo.p21_view_oe_hdr.ship2_add1,
P21PLAY.dbo.p21_view_oe_hdr.ship2_add2,
P21PLAY.dbo.p21_view_oe_hdr.ship2_city,
P21PLAY.dbo.p21_view_oe_hdr.ship2_state,
P21PLAY.dbo.p21_view_oe_hdr.ship2_zip,
P21PLAY.dbo.p21_view_oe_hdr.po_no,
P21PLAY.dbo.p21_view_oe_pick_ticket.carrier_id AS Carrier,
P21PLAY.dbo.p21_view_oe_pick_ticket.carrier_id AS Supplier,
P21PLAY.dbo.p21_view_oe_hdr.cod_flag,
P21PLAY.dbo.p21_view_oe_hdr.terms,
P21PLAY.dbo.p21_view_oe_hdr.ship2_country,
P21PLAY.dbo.p21_view_oe_hdr.ship_to_phone,
P21PLAY.dbo.p21_view_oe_hdr.delivery_instructions,
dbo.Address_Table.ups_code,
-----------Looks for special character and returns next 6 spaces as UPS_Shipper----------
SUBSTRING(P21PLAY.dbo.p21_view_oe_hdr.delivery_instructions, PATINDEX('%[^a-z ]%',
P21PLAY.dbo.p21_view_oe_hdr.delivery_instructions) + 1, 6)
AS UPS_Shipper,
------------------Checks view for email address or assigns alternate------------------
(CASE WHEN charindex('@', p21_view_contacts.email_address) > 0 THEN p21_view_contacts.email_address ELSE
'email@domain.com' END) AS alternate_address,
'Y' AS QVN, 'email@domain.com' AS failureaddress,
P21PLAY.dbo.p21_view_contacts.email_address,
------------When carrier_id is not one of these # then Null; else ------------------------------
CASE WHEN P21PLAY.dbo.p21_view_oe_pick_ticket.carrier_id NOT IN (105188, 105191, 105194, 105197, 105200,
105203, 105206, 105209, 105212) THEN NULL
----------------- Looks for special Character in delivery_instructions; if NULL then ups_code;
ELSE return value from delivery_instructions as UPS_Final--------------------
WHEN SUBSTRING(P21PLAY.dbo.p21_view_oe_hdr.delivery_instructions,
PATINDEX('%[^a-z ]%', P21PLAY.dbo.p21_view_oe_hdr.delivery_instructions) + 1, 6) IS NULL
THEN dbo.Address_Table.ups_code
ELSE SUBSTRING(P21PLAY.dbo.p21_view_oe_hdr.delivery_instructions,
PATINDEX('%[^a-z ]%', P21PLAY.dbo.p21_view_oe_hdr.delivery_instructions) + 1, 6) END AS UPS_FINAL
FROM dbo.Address_Table INNER JOIN
P21PLAY.dbo.p21_view_oe_pick_ticket INNER JOIN
P21PLAY.dbo.p21_view_oe_hdr ON P21PLAY.dbo.p21_view_oe_pick_ticket.order_no =
P21PLAY.dbo.p21_view_oe_hdr.order_no ON
dbo.Address_Table.id = P21PLAY.dbo.p21_view_oe_hdr.customer_id LEFT OUTER JOIN
P21PLAY.dbo.p21_view_contacts ON P21PLAY.dbo.p21_view_oe_hdr.contact_id = P21PLAY.dbo.p21_view_contacts.id
WHERE
(P21PLAY.dbo.p21_view_oe_hdr.completed <> 'Y')
AND (P21PLAY.dbo.p21_view_oe_hdr.delete_flag <> 'Y')
AND (P21PLAY.dbo.p21_view_oe_hdr.will_call <> 'Y')
ORDER BY P21PLAY.dbo.p21_view_oe_pick_ticket.pick_ticket_no
Hope this makes since
December 11, 2007 at 3:43 pm
Either PATINDEX or CHARINDEX should work for you, just use the position of the pound sign to determine what the start pos and length will be. If other characters beside the pound sign are used, you may want to replace the special characters before parsing. I find it eaiser to read with charindex as oposed to patindex
Example
DECLARE @vcValue VARCHAR(255)
SET @vcValue = 'THIS IS MY FIELD DATA, BUT I ONLY WANT THE DATA AFTER THE #SIGN'
SELECT substring(@vcValue,charindex('#',@vcValue,1)+1,len(@vcValue))
RETURNS "SIGN"
SET @vcValue = 'THIS IS MY FIELD DATA, BUT I ONLY WANT THE DATA BETWEEN THE #SIGNS# EVEN WHEN MORE DATA FOLLOWS'
SELECT substring(@vcValue,charindex('#',@vcValue,1)+1,charindex('#',@vcValue,charindex('#',@vcValue,1)+1)-(charindex('#',@vcValue,1)+1))
RETURNS "SIGNS"
-
December 11, 2007 at 5:39 pm
Some more info here: http://qa.sqlservercentral.com/articles/Advanced+Querying/tamestrings2.asp/97/
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
December 11, 2007 at 7:17 pm
Just a thought... what actually forces the user to enter a "#" sign before the number?
--Jeff Moden
December 12, 2007 at 8:48 am
Hey guys thanks for the reply:
In the program we are using I have a field called "UPS Shipping Instructions"
This field can contain anything value so the users go free-for-all on it, but the programs doesnt allow us to put a "one time" UPS account number for our customers in special situations. So I force the users to enter a # before putting the UPS number so that its unique.
Im currently taking the Charindex in the second post (thanks you so much) to edit it so that my users will have to put UPS Code#999999# and it will only take 999999
In the current Charindex im running it will take whatever is after the # sign but it the field does not contain the # then it grabs the first word or some pretty random stuff.
Im so new to SQL and have to learn it for my company, im working through some tutorials and whatnot but im forced to write things out of my skillset because of time 🙁
I really appreciate the help though !!!!!... boy do i need it
December 14, 2007 at 10:44 am
ok....
So I have been reading up on my Charindex's and whatnot and with the code that I have been giving. I have come up with this Charindex for grabbing whatever is between #999999# to return 999999 as a substring. When I run this I get an error :
"Invalid Length Parameter passed to Subtring Function"
Im not exactly sure why I get this error because I though the substring function searched my field and then returned the correct substring.
SUBSTRING(P21PLAY.dbo.p21_view_oe_hdr.delivery_instructions,
CHARINDEX('#', P21PLAY.dbo.p21_view_oe_hdr.delivery_instructions, 1) + 1,
CHARINDEX('#', P21PLAY.dbo.p21_view_oe_hdr.delivery_instructions,
CHARINDEX('#', P21PLAY.dbo.p21_view_oe_hdr.delivery_instructions, 1) + 1)
- (CHARINDEX('#', P21PLAY.dbo.p21_view_oe_hdr.delivery_instructions, 1) + 1))
AS UPSTEST,
Any help is greatly appreciate 🙂
December 14, 2007 at 11:54 am
Can you send a samle of the data found in P21PLAY.dbo.p21_view_oe_hdr.delivery_instructions?
I believe your data will be something like
"BUNCHA STUFF #999999 OTHER STUFF"
The error is probably occuring because there is no # sign after the first # sign in one or more of your rows. But it would help if you had a sample line to share (with sensitive data removed of course)
-
December 14, 2007 at 1:30 pm
Yeah sorry I should have included that info:
The delivery instructions field will contain many different types of data such as :
UPS delivery next day acct num #999999# or
UPS FAST #999999 or
Deliver next day please
So I guess you question answers my initial question because sometimes our users will put in #999999 as opposed to #999999# because they are forgetful or even put in instruction without a # at all...........
I realize that it will return a string that is huge, so im assuming the way I have it now is as good as it gets with this field since it has so many variables
Antony
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply