Blank spaces in the middle of text (parsing)

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

General SQL Server Forums

New to SQL Server Programming

Blank spaces in the middle of text (parsing)

Author

Topic

pwvailla
Starting Member

31 Posts

Posted - 2010-11-17 : 16:51:47

Hi, I have a large text field used by the Purchasing Department. They stuff PO# and PO Line Number into this field, then a bunch of crap I don't care about.

I need to be able to parse this text string and find where a blank space exists in the middle of the text string (So RTRIM and LTRIM is not useful and "_" or "%" wildcards are not specific enough). I need to know that a blank space exists in a particular position (ie. 7th or 8th character). Here's an example of the text field ATTN:

ATTN FIELD DATA
--------------------------
298933 06700 ML-557T GREE
298926 01300 ML-A037 DEER
298947 04400 ML-969F RABI
298945 04800 ML-969F RABI
298946 04400 ML-969F RABI
298948 04400 ML-969F RABI
337X596 01100 ML-E5JA KUR
337X599 01000 ML-E5JA KUR
962X019 00900 ML-E5JA IMM
962X020 01000 ML-E5JACHP

I care about everything before the ML, etc. as they represent PO# and PO Line number. So, 962X020 = PO# and 01000 = PO Line number in the last example.

It seems I can parse the field like this:
WHERE attn LIKE '[0-9][0-9][0-9]_[0-9][0-9][0-9]_[0-9][0-9][0-9][0-9][0-9]%

or
attn LIKE '[0-9][0-9][0-9][0-9][0-9][0-9]_[0-9][0-9][0-9][0-9][0-9]%

My question is:

1.Is there are specific way to look for a "space" instead of using a wildcard "_" in the 4th and 8th position of the 1st ATTN example or the 7th position in the 2nd example? and

2. Is there a good code construct to use in the WHERE clause to take care of both WHERE clauses <#1 clause> OR <#2 clause>?

Thanks

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-11-17 : 17:32:34

quote:
Is there are specific way to look for a "space" instead of using a wildcard "_" in the 4th and 8th position of the 1st ATTN example or the 7th position in the 2nd example?

Yes, just put a space in the pattern where you want to check for it, instead of an underscore:

WHERE attn LIKE '[0-9][0-9][0-9]_[0-9][0-9][0-9] [0-9][0-9][0-9][0-9][0-9]%

quote:
Is there a good code construct to use in the WHERE clause to take care of both WHERE clauses <#1 clause> OR <#2 clause>?

Remarkably, yes:

WHERE attn LIKE '[0-9][0-9][0-9]_[0-9][0-9][0-9] [0-9][0-9][0-9][0-9][0-9]%
OR attn LIKE '[0-9][0-9][0-9][0-9][0-9][0-9] [0-9][0-9][0-9][0-9][0-9]%

Wish I could take credit for this, but you essentially answered your own questions.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-11-18 : 06:16:51

<<
Wish I could take credit for this,
>>

Yes. You have already given a clue
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=151936

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-11-18 : 07:01:31

you can just use like this if format is consistent


SELECT PARSENAME(REPLACE(RTRIM(LEFT([ATTN FIELD DATA],CHARINDEX('ML',[ATTN FIELD DATA])-1)),' ','.'),1) AS [PO Line number],
PARSENAME(REPLACE(RTRIM(LEFT([ATTN FIELD DATA],CHARINDEX('ML',[ATTN FIELD DATA])-1)),' ','.'),2) AS [PO#]
FROM Table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources