November 5, 2013 at 9:44 am
I have a customer that is upgrading his legacy to updated version of his software and I have to develop a Shipper document in the new system. My issue is the comments that the Shipper needs.
In the legacy system the field that was used for comments was NVARCHAR(30) and for the customer to know what to bring into the doc they would use a '~' and write the procedure accordingly. There could be multiple lines for the Shipping Number. Now in the new system the comments field is NVARChAR(MAX) and all the comments have been put into one line for that Shipping Number. So there can be more then one '~' in the field and there is no rhyme or reason, they can be anywhere, as many times, in the field. I need a UDF or Procedure to look for the '~' in the field and pull out text between '~' and '<'.
Example data
Legacy
RowNumberSONbrComment
1 S070358
2 S0703338777
3 S070REPAIR
4 S070~U.S. GOVT UNIT
5 S070~NSN: 1650-01-474-4638 TGS PN: 42555-43G
6 S070~CONTRACT: N00383-10-D-003N
7 S070~SHIPTO: SOME ADDRESS
New System
RowNumber SONbrComment
1 S0700358 < 3338777 < REPAIR < ~U.S. GOVT UNIT < ~NSN: 1650-01-474-4638 TGS PN: 42555-43G < ~CONTRACT: N00383-10-D-003N < ~SHIPTO: SOME ADDRESS
Can anyone point me in the right direction, a website, a blog...anything really? I usually write straight SQL for a view, although I have written procedures and UDFs before, nothing like this.
Thanks in advanced,
Kerrie
November 5, 2013 at 9:58 am
No problem. Take a look at the link in my signature about splitting strings. In there you will find the DelimitedSplit8K iTVF.
Please notice how I posted ddl and sample data in a readily consumable format. Your code would be something like this:
create table #Something
(
RowNum int,
SONbr char(4),
Comment varchar(200)
)
insert #Something
select 1, 'S070', '0358 < 3338777 < REPAIR < ~U.S. GOVT UNIT < ~NSN: 1650-01-474-4638 TGS PN: 42555-43G < ~CONTRACT: N00383-10-D-003N < ~SHIPTO: SOME ADDRESS'
select *, LTRIM(Item)
from #Something
cross apply dbo.DelimitedSplit8K(Comment, '<')
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 5, 2013 at 10:00 am
I am now thinking that perhaps I did this backwards. You have everything split out and you want to pack it all together. Take a look at this article for an explanation of how to do that.
http://www.sqlservercentral.com/articles/71700/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply