SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 difficulty with substring and CHAR(13)
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

codelphi
Starting Member

USA
2 Posts

Posted - 04/04/2014 :  18:01:11  Show Profile  Reply with Quote
I am trying to extract data from a text string and as near as I can tell I have it except for problems with carriage returns.

examples of the data are as follows

DMSERVER-web 01/16/09 - Deal Completed and Finalized by Fred Beans GM DM-011609-46 b4- 490 pcs 86 lines $2068.02 Aft - 10 pcs 7 lines $252.41 <email address>

DMSERVER-web 1/16/2009 3:15:02 PM Sent To Seller Carfagno Chevrolet - DM-011609-46 / Steve Hartley / <phone#>, po- JAB375 aft - 10pcs 7 lines $252.41 Amt b4 - DM-011609-46/490 pcs 86 lines $252.41 Aft - 10 pcs 7 lines $252.41 <email address>

DMSERVER-web 01/16/09 - Deal Completed and Finalized by Evergreen Ford DM-011609-36 b4- 72 pcs 18 lines $720.41 Aft - 21 pcs 6 lines $681.14 <email address>

DMSERVER-web 1/16/2009 1:59:02 PM Sent To Seller Ultimate Ford - DM-011609-36 / Rebecca Guthrie / <phone#>, po- aft - 21pcs 6 lines $681.14 Amt b4 - DM-011609-36/72 pcs 18 lines $681.14 Aft - 21 pcs 6 lines $681.14 <email address>

DMSERVER-web 12/22/2008 11:37:57 AM Sent To Seller Southland Ford - DM-122208-10-69 / Chris Goodman / <phone#> aft - 4pcs 4 lines $275.56 Amt b4 - DM-122208-10-69/4 pcs 4 lines $275.56 Aft - 4 pcs 4 lines $275.56 <email address>

I am trying to extract the DM-######-## (the length varies but you get the idea)

I have started with a view with this select statement
SELECT     SUBSTRING(NOTES, CHARINDEX('dm-', NOTES), 25)as bob


and it trims the results to this

DM-110909-56
b4

DM-110909-56 /

DM-110509-101
b

DM-110509-101

DM-110609-120
b

with a subsequent view of this

SELECT     SUBSTRING(bob, CHARINDEX(CHAR(13), bob) + 1, CHARINDEX(' ', bob)) AS phil
FROM         dbo.vw1stnotes


the results look like this

DM-011609-46
b4-

DM-011609-46

DM-011609-36
b4-

DM-011609-36

DM-122208-10-69

But so far any attempt the use substring with the end character being CHAR(13) to remove the "b4" values has failed. Does anyone have any suggestions or a different method than what I'm currently using that will get me the results I want?

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 04/07/2014 :  09:04:32  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Can you try this?

SELECT     SUBSTRING(bob, PATINDEX(bob,'%DM-[0-9][0-9][0-9][0-9][0-9][0-9]-[0-9][0-9]%,12)) AS phil
FROM         dbo.vw1stnotes


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

codelphi
Starting Member

USA
2 Posts

Posted - 04/07/2014 :  10:44:54  Show Profile  Reply with Quote
I thought of that as well but if you notice
1st the DM-######- is static for size, but the following number can be a ## or ###, and as in the last example is
DM-122208-10-69 and under extreme circumstances can be DM-######-###-###. So although a great suggestion, it does not work.
Go to Top of Page

Ifor
Aged Yak Warrior

590 Posts

Posted - 04/07/2014 :  12:01:04  Show Profile  Reply with Quote

-- *** Test Data ***
CREATE TABLE #t
(
	Notes varchar(255) NOT NULL
);
INSERT INTO #t
SELECT 'DMSERVER-web 01/16/09 - Deal Completed and Finalized by Fred Beans GM DM-011609-46 b4- 490 pcs 86 lines $2068.02 Aft - 10 pcs 7 lines $252.41 <email address>'
UNION ALL SELECT 'DMSERVER-web 1/16/2009 3:15:02 PM Sent To Seller Carfagno Chevrolet - DM-011609-46 / Steve Hartley / <phone#>, po- JAB375 aft - 10pcs 7 lines $252.41 Amt b4 - DM-011609-46/490 pcs 86 lines $252.41 Aft - 10 pcs 7 lines $252.41 <email address>'
UNION ALL SELECT 'DMSERVER-web 01/16/09 - Deal Completed and Finalized by Evergreen Ford DM-011609-36 b4- 72 pcs 18 lines $720.41 Aft - 21 pcs 6 lines $681.14 <email address>'
UNION ALL SELECT 'DMSERVER-web 1/16/2009 1:59:02 PM Sent To Seller Ultimate Ford - DM-011609-36 / Rebecca Guthrie / <phone#>, po- aft - 21pcs 6 lines $681.14 Amt b4 - DM-011609-36/72 pcs 18 lines $681.14 Aft - 21 pcs 6 lines $681.14 <email address>'
UNION ALL SELECT 'DMSERVER-web 12/22/2008 11:37:57 AM Sent To Seller Southland Ford - DM-122208-10-69 / Chris Goodman / <phone#> aft - 4pcs 4 lines $275.56 Amt b4 - DM-122208-10-69/4 pcs 4 lines $275.56 Aft - 4 pcs 4 lines $275.56 <email address>';
-- *** End Test Data ***

WITH PartNotes(PNote)
AS
(
	SELECT SUBSTRING(Notes, CHARINDEX('DM-', Notes), 18)
	FROM #t
)
SELECT LEFT(PNote,  PATINDEX('%[^A-Z,0-9,-]%', PNote) -1) AS Result
FROM PartNotes;
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000