Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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)
 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
22864 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

700 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  
 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.03 seconds. Powered By: Snitz Forums 2000