difficulty with substring and CHAR(13)

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

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

difficulty with substring and CHAR(13)

Author

Topic

codelphi
Starting Member

2 Posts

Posted - 2014-04-04 : 18:01:11

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

22864 Posts

Posted - 2014-04-07 : 09:04:32

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

codelphi
Starting Member

2 Posts

Posted - 2014-04-07 : 10:44:54

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.

Ifor
Aged Yak Warrior

700 Posts

Posted - 2014-04-07 : 12:01:04

[code]
-- *** 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;
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources