Formating data in View

[NoncentztheGreat]

I am looking to format a field in my view that does the following:

If I have a field that looks like this
---------------------------------------------------
pay to customer #123456 freight code 0
----------------------------------------------------
name of field: oe_hdr.delivery_instructions (if this makes a difference??)

What i am looking to do make is so my view will look for the # sign..(or really any special character is # doesnt work) and grab the next 6 digits ONLY from the field and show it in my view. Im new to SQL and im still learing how to join views so needless to say im a nub!!!

Can anyone help me out with this... Also if anyone knows of any good learing resources that would be awesome

 

[AlexCuse]

Have a look at this:

[COLOR=blue]declare[/color] @n [COLOR=blue]varchar[/color](100)
[COLOR=blue]set[/color] @n = [COLOR=red]'pay to customer #123456 freight code 0'[/color]

[COLOR=blue]select[/color] @n, [COLOR=#FF00FF]substring[/color](@n, [COLOR=#FF00FF]charindex[/color]([COLOR=red]'#'[/color], @n), 7)

And read Books Online for CHARINDEX and SUBSTRING, both are good to know.

Hope it helps,

ALex



[small]----signature below----[/small]
Now you can go where the people are one!
Now you can go where they get things done!

 

[gmmastros]

Add this to your view.

[tt][blue]SubString(oe_hdr.delivery_instructions, PatIndex('%[^a-z ]%', oe_hdr.delivery_instructions) + 1, 6) As CustomerNumber[/blue][/tt]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[NoncentztheGreat]

Wow thanks for the quick replies: I decided to go with declaring the variable first and im kinda lost as to where i insert my code, this is what I have currently:
-----------------------------------------------------------
DECLARE @n varchar(100)
set @n = oe_hdr.delivery_instructions

SELECT

select @n, substring(@n, charindex('#', @n), 7)

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, P21PLAY.dbo.p21_view_contacts.email_address,
(CASE WHEN charindex('@', [email_address]) > 0 THEN [email_address] ELSE 'test1@test2.com' END) AS alternate_address, 'Y' AS QVN,'test1@test2.com' AS failureaddress

FROM 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 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_pick_ticket.ship_date IS NULL)
----------------------------------------------------------

From what I understand i define my variable first and set it to my field (i would like to get the entire field because the user could put in anything)

Then in the select statement I grab that variable "n" and search for # and take the next 6 digits correct???

But when i execute i get an error... im sure im off here somewhere

 

[gmmastros]

You cannot use variables within a view.

Try this...

SELECT 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, 
       SubString(oe_hdr.delivery_instructions, PatIndex('%[^a-z ]%', oe_hdr.delivery_instructions) + 1, 6) As CustomerNumber,
       P21PLAY.dbo.p21_view_contacts.email_address,
       (CASE WHEN charindex('@', [email_address]) > 0 THEN [email_address] ELSE 'email@domain.com' END) AS alternate_address, 
       'Y' AS QVN,
       'email@domain.com' AS failureaddress
FROM   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 
       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_pick_ticket.ship_date IS NULL)

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[NoncentztheGreat]

Freakin wow.... George that worked perfectly for what I need to accomplish!!!!!!