substring and patindex HELP! - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

substring and patindex HELP!

Author

Topic

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2007-01-31 : 09:15:51

I must need more sleep...
We have a table IM_INV which store item location info for each item in col BIN_1. The data in this column is varchar and follows this pattern:
'A1-1A','A1-1B','A1-1C','A1-1D','A1-2A',etc...
If I use

SELECT  ITEM_NO,BIN_1,SUBSTRING(BIN_1, 1, 2),
SUBSTRING(BIN_1, 2, 1),
SUBSTRING(BIN_1, 3, 1),
SUBSTRING(BIN_1, 4, 1),
SUBSTRING(BIN_1, 5, 1)
FROM IM_INV

The result is something like:

 item_no    bin_1     col1     col2     col3     col4     col5
--------------------------------------------------------------------
  1234     A1-1A      A         1        -        1       A

I get a column for each character, which is fine. Trouble is, when the BIN_1 data gets to 'A10-1A' or 'A1-15D' then this substring query doesn't work. On top of that there is also data like 'ZZ1-10'.
So now I am stumped. What I need to do is select all the leftmost character(s) into a column, then the following digits left of the '-', then the digits to the right of the'-', and finally the rightmost characters for a total of 4 columns. 4 dimentional gridding of inventory in one column; ugh! Anyway, I'm not very good with PATINDEX, and I'm not even sure if that is the way to go for this. Can someone guide this lost soul?
Andy

There's never enough time to type code right,
but always enough time for a hotfix...

There's never enough time to type code right,
but always enough time for a hotfix...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-31 : 10:20:37

[code]-- prepare sample data
declare @sample table (coord varchar(10))

insert @sample
select 'A1-1A' union all
select 'A1-1B' union all
select 'A1-1C' union all
select 'A1-1D' union all
select 'A1-2A' union all
select 'A10-1A' union all
select 'A1-15D' union all
select 'ZZ1-10'

-- show the result very fast!
SELECT NULL AS p1, NULL AS p2, '-' AS d, NULL AS p3, NULL AS p4 FROM @sample WHERE coord LIKE '-'
UNION ALL
SELECT NULL, NULL, '-', NULL, SUBSTRING(coord, 2, 1) FROM @sample WHERE coord LIKE '-[a-z]'
UNION ALL
SELECT NULL, NULL, '-', NULL, SUBSTRING(coord, 2, 2) FROM @sample WHERE coord LIKE '-[a-z][a-z]'
UNION ALL
SELECT NULL, NULL, '-', SUBSTRING(coord, 2, 1), NULL FROM @sample WHERE coord LIKE '-[0-9]'
UNION ALL
SELECT NULL, NULL, '-', SUBSTRING(coord, 2, 1), SUBSTRING(coord, 3, 1) FROM @sample WHERE coord LIKE '-[0-9][a-z]'
UNION ALL
SELECT NULL, NULL, '-', SUBSTRING(coord, 2, 1), SUBSTRING(coord, 3, 2) FROM @sample WHERE coord LIKE '-[0-9][a-z][a-z]'
UNION ALL
SELECT NULL, NULL, '-', SUBSTRING(coord, 2, 2), NULL FROM @sample WHERE coord LIKE '-[0-9][0-9]'
UNION ALL
SELECT NULL, NULL, '-', SUBSTRING(coord, 2, 2), SUBSTRING(coord, 4, 1) FROM @sample WHERE coord LIKE '-[0-9][0-9][a-z]'
UNION ALL
SELECT NULL, NULL, '-', SUBSTRING(coord, 2, 2), SUBSTRING(coord, 4, 2) FROM @sample WHERE coord LIKE '-[0-9][0-9][a-z][a-z]'
UNION ALL
SELECT NULL, SUBSTRING(coord, 1, 1), '-', NULL, NULL FROM @sample WHERE coord LIKE '[0-9]-'
UNION ALL
SELECT NULL, SUBSTRING(coord, 1, 1), '-', NULL, SUBSTRING(coord, 3, 1) FROM @sample WHERE coord LIKE '[0-9]-[a-z]'
UNION ALL
SELECT NULL, SUBSTRING(coord, 1, 1), '-', NULL, SUBSTRING(coord, 3, 2) FROM @sample WHERE coord LIKE '[0-9]-[a-z][a-z]'
UNION ALL
SELECT NULL, SUBSTRING(coord, 1, 1), '-', SUBSTRING(coord, 3, 1), NULL FROM @sample WHERE coord LIKE '[0-9]-[0-9]'
UNION ALL
SELECT NULL, SUBSTRING(coord, 1, 1), '-', SUBSTRING(coord, 3, 1), SUBSTRING(coord, 4, 1) FROM @sample WHERE coord LIKE '[0-9]-[0-9][a-z]'
UNION ALL
SELECT NULL, SUBSTRING(coord, 1, 1), '-', SUBSTRING(coord, 3, 1), SUBSTRING(coord, 4, 2) FROM @sample WHERE coord LIKE '[0-9]-[0-9][a-z][a-z]'
UNION ALL
SELECT NULL, SUBSTRING(coord, 1, 1), '-', SUBSTRING(coord, 3, 2), NULL FROM @sample WHERE coord LIKE '[0-9]-[0-9][0-9]'
UNION ALL
SELECT NULL, SUBSTRING(coord, 1, 1), '-', SUBSTRING(coord, 3, 2), SUBSTRING(coord, 5, 1) FROM @sample WHERE coord LIKE '[0-9]-[0-9][0-9][a-z]'
UNION ALL
SELECT NULL, SUBSTRING(coord, 1, 1), '-', SUBSTRING(coord, 3, 2), SUBSTRING(coord, 5, 2) FROM @sample WHERE coord LIKE '[0-9]-[0-9][0-9][a-z][a-z]'
UNION ALL
SELECT NULL, SUBSTRING(coord, 1, 2), '-', NULL, NULL FROM @sample WHERE coord LIKE '[0-9][0-9]-'
UNION ALL
SELECT NULL, SUBSTRING(coord, 1, 2), '-', NULL, SUBSTRING(coord, 4, 1) FROM @sample WHERE coord LIKE '[0-9][0-9]-[a-z]'
UNION ALL
SELECT NULL, SUBSTRING(coord, 1, 2), '-', NULL, SUBSTRING(coord, 4, 2) FROM @sample WHERE coord LIKE '[0-9][0-9]-[a-z][a-z]'
UNION ALL
SELECT NULL, SUBSTRING(coord, 1, 2), '-', SUBSTRING(coord, 4, 1), NULL FROM @sample WHERE coord LIKE '[0-9][0-9]-[0-9]'
UNION ALL
SELECT NULL, SUBSTRING(coord, 1, 2), '-', SUBSTRING(coord, 4, 1), SUBSTRING(coord, 5, 1) FROM @sample WHERE coord LIKE '[0-9][0-9]-[0-9][a-z]'
UNION ALL
SELECT NULL, SUBSTRING(coord, 1, 2), '-', SUBSTRING(coord, 4, 1), SUBSTRING(coord, 5, 2) FROM @sample WHERE coord LIKE '[0-9][0-9]-[0-9][a-z][a-z]'
UNION ALL
SELECT NULL, SUBSTRING(coord, 1, 2), '-', SUBSTRING(coord, 4, 2), NULL FROM @sample WHERE coord LIKE '[0-9][0-9]-[0-9][0-9]'
UNION ALL
SELECT NULL, SUBSTRING(coord, 1, 2), '-', SUBSTRING(coord, 4, 2), SUBSTRING(coord, 6, 1) FROM @sample WHERE coord LIKE '[0-9][0-9]-[0-9][0-9][a-z]'
UNION ALL
SELECT NULL, SUBSTRING(coord, 1, 2), '-', SUBSTRING(coord, 4, 2), SUBSTRING(coord, 6, 2) FROM @sample WHERE coord LIKE '[0-9][0-9]-[0-9][0-9][a-z][a-z]'
UNION ALL
SELECT SUBSTRING(coord, 1, 1), NULL, '-', NULL, NULL FROM @sample WHERE coord LIKE '[a-z]-'
UNION ALL
SELECT SUBSTRING(coord, 1, 1), NULL, '-', NULL, SUBSTRING(coord, 3, 1) FROM @sample WHERE coord LIKE '[a-z]-[a-z]'
UNION ALL
SELECT SUBSTRING(coord, 1, 1), NULL, '-', NULL, SUBSTRING(coord, 3, 2) FROM @sample WHERE coord LIKE '[a-z]-[a-z][a-z]'
UNION ALL
SELECT SUBSTRING(coord, 1, 1), NULL, '-', SUBSTRING(coord, 3, 1), NULL FROM @sample WHERE coord LIKE '[a-z]-[0-9]'
UNION ALL
SELECT SUBSTRING(coord, 1, 1), NULL, '-', SUBSTRING(coord, 3, 1), SUBSTRING(coord, 4, 1) FROM @sample WHERE coord LIKE '[a-z]-[0-9][a-z]'
UNION ALL
SELECT SUBSTRING(coord, 1, 1), NULL, '-', SUBSTRING(coord, 3, 1), SUBSTRING(coord, 4, 2) FROM @sample WHERE coord LIKE '[a-z]-[0-9][a-z][a-z]'
UNION ALL
SELECT SUBSTRING(coord, 1, 1), NULL, '-', SUBSTRING(coord, 3, 2), NULL FROM @sample WHERE coord LIKE '[a-z]-[0-9][0-9]'
UNION ALL
SELECT SUBSTRING(coord, 1, 1), NULL, '-', SUBSTRING(coord, 3, 2), SUBSTRING(coord, 5, 1) FROM @sample WHERE coord LIKE '[a-z]-[0-9][0-9][a-z]'
UNION ALL
SELECT SUBSTRING(coord, 1, 1), NULL, '-', SUBSTRING(coord, 3, 2), SUBSTRING(coord, 5, 2) FROM @sample WHERE coord LIKE '[a-z]-[0-9][0-9][a-z][a-z]'
UNION ALL
SELECT SUBSTRING(coord, 1, 1), SUBSTRING(coord, 2, 1), '-', NULL, NULL FROM @sample WHERE coord LIKE '[a-z][0-9]-'
UNION ALL
SELECT SUBSTRING(coord, 1, 1), SUBSTRING(coord, 2, 1), '-', NULL, SUBSTRING(coord, 4, 1) FROM @sample WHERE coord LIKE '[a-z][0-9]-[a-z]'
UNION ALL
SELECT SUBSTRING(coord, 1, 1), SUBSTRING(coord, 2, 1), '-', NULL, SUBSTRING(coord, 4, 2) FROM @sample WHERE coord LIKE '[a-z][0-9]-[a-z][a-z]'
UNION ALL
SELECT SUBSTRING(coord, 1, 1), SUBSTRING(coord, 2, 1), '-', SUBSTRING(coord, 4, 1), NULL FROM @sample WHERE coord LIKE '[a-z][0-9]-[0-9]'
UNION ALL
SELECT SUBSTRING(coord, 1, 1), SUBSTRING(coord, 2, 1), '-', SUBSTRING(coord, 4, 1), SUBSTRING(coord, 5, 1) FROM @sample WHERE coord LIKE '[a-z][0-9]-[0-9][a-z]'
UNION ALL
SELECT SUBSTRING(coord, 1, 1), SUBSTRING(coord, 2, 1), '-', SUBSTRING(coord, 4, 1), SUBSTRING(coord, 5, 2) FROM @sample WHERE coord LIKE '[a-z][0-9]-[0-9][a-z][a-z]'
UNION ALL
SELECT SUBSTRING(coord, 1, 1), SUBSTRING(coord, 2, 1), '-', SUBSTRING(coord, 4, 2), NULL FROM @sample WHERE coord LIKE '[a-z][0-9]-[0-9][0-9]'
UNION ALL
SELECT SUBSTRING(coord, 1, 1), SUBSTRING(coord, 2, 1), '-', SUBSTRING(coord, 4, 2), SUBSTRING(coord, 6, 1) FROM @sample WHERE coord LIKE '[a-z][0-9]-[0-9][0-9][a-z]'
UNION ALL
SELECT SUBSTRING(coord, 1, 1), SUBSTRING(coord, 2, 1), '-', SUBSTRING(coord, 4, 2), SUBSTRING(coord, 6, 2) FROM @sample WHERE coord LIKE '[a-z][0-9]-[0-9][0-9][a-z][a-z]'
UNION ALL
SELECT SUBSTRING(coord, 1, 1), SUBSTRING(coord, 2, 2), '-', NULL, NULL FROM @sample WHERE coord LIKE '[a-z][0-9][0-9]-'
UNION ALL
SELECT SUBSTRING(coord, 1, 1), SUBSTRING(coord, 2, 2), '-', NULL, SUBSTRING(coord, 5, 1) FROM @sample WHERE coord LIKE '[a-z][0-9][0-9]-[a-z]'
UNION ALL
SELECT SUBSTRING(coord, 1, 1), SUBSTRING(coord, 2, 2), '-', NULL, SUBSTRING(coord, 5, 2) FROM @sample WHERE coord LIKE '[a-z][0-9][0-9]-[a-z][a-z]'
UNION ALL
SELECT SUBSTRING(coord, 1, 1), SUBSTRING(coord, 2, 2), '-', SUBSTRING(coord, 5, 1), NULL FROM @sample WHERE coord LIKE '[a-z][0-9][0-9]-[0-9]'
UNION ALL
SELECT SUBSTRING(coord, 1, 1), SUBSTRING(coord, 2, 2), '-', SUBSTRING(coord, 5, 1), SUBSTRING(coord, 6, 1) FROM @sample WHERE coord LIKE '[a-z][0-9][0-9]-[0-9][a-z]'
UNION ALL
SELECT SUBSTRING(coord, 1, 1), SUBSTRING(coord, 2, 2), '-', SUBSTRING(coord, 5, 1), SUBSTRING(coord, 6, 2) FROM @sample WHERE coord LIKE '[a-z][0-9][0-9]-[0-9][a-z][a-z]'
UNION ALL
SELECT SUBSTRING(coord, 1, 1), SUBSTRING(coord, 2, 2), '-', SUBSTRING(coord, 5, 2), NULL FROM @sample WHERE coord LIKE '[a-z][0-9][0-9]-[0-9][0-9]'
UNION ALL
SELECT SUBSTRING(coord, 1, 1), SUBSTRING(coord, 2, 2), '-', SUBSTRING(coord, 5, 2), SUBSTRING(coord, 7, 1) FROM @sample WHERE coord LIKE '[a-z][0-9][0-9]-[0-9][0-9][a-z]'
UNION ALL
SELECT SUBSTRING(coord, 1, 1), SUBSTRING(coord, 2, 2), '-', SUBSTRING(coord, 5, 2), SUBSTRING(coord, 7, 2) FROM @sample WHERE coord LIKE '[a-z][0-9][0-9]-[0-9][0-9][a-z][a-z]'
UNION ALL
SELECT SUBSTRING(coord, 1, 2), NULL, '-', NULL, NULL FROM @sample WHERE coord LIKE '[a-z][a-z]-'
UNION ALL
SELECT SUBSTRING(coord, 1, 2), NULL, '-', NULL, SUBSTRING(coord, 4, 1) FROM @sample WHERE coord LIKE '[a-z][a-z]-[a-z]'
UNION ALL
SELECT SUBSTRING(coord, 1, 2), NULL, '-', NULL, SUBSTRING(coord, 4, 2) FROM @sample WHERE coord LIKE '[a-z][a-z]-[a-z][a-z]'
UNION ALL
SELECT SUBSTRING(coord, 1, 2), NULL, '-', SUBSTRING(coord, 4, 1), NULL FROM @sample WHERE coord LIKE '[a-z][a-z]-[0-9]'
UNION ALL
SELECT SUBSTRING(coord, 1, 2), NULL, '-', SUBSTRING(coord, 4, 1), SUBSTRING(coord, 5, 1) FROM @sample WHERE coord LIKE '[a-z][a-z]-[0-9][a-z]'
UNION ALL
SELECT SUBSTRING(coord, 1, 2), NULL, '-', SUBSTRING(coord, 4, 1), SUBSTRING(coord, 5, 2) FROM @sample WHERE coord LIKE '[a-z][a-z]-[0-9][a-z][a-z]'
UNION ALL
SELECT SUBSTRING(coord, 1, 2), NULL, '-', SUBSTRING(coord, 4, 2), NULL FROM @sample WHERE coord LIKE '[a-z][a-z]-[0-9][0-9]'
UNION ALL
SELECT SUBSTRING(coord, 1, 2), NULL, '-', SUBSTRING(coord, 4, 2), SUBSTRING(coord, 6, 1) FROM @sample WHERE coord LIKE '[a-z][a-z]-[0-9][0-9][a-z]'
UNION ALL
SELECT SUBSTRING(coord, 1, 2), NULL, '-', SUBSTRING(coord, 4, 2), SUBSTRING(coord, 6, 2) FROM @sample WHERE coord LIKE '[a-z][a-z]-[0-9][0-9][a-z][a-z]'
UNION ALL
SELECT SUBSTRING(coord, 1, 2), SUBSTRING(coord, 3, 1), '-', NULL, NULL FROM @sample WHERE coord LIKE '[a-z][a-z][0-9]-'
UNION ALL
SELECT SUBSTRING(coord, 1, 2), SUBSTRING(coord, 3, 1), '-', NULL, SUBSTRING(coord, 5, 1) FROM @sample WHERE coord LIKE '[a-z][a-z][0-9]-[a-z]'
UNION ALL
SELECT SUBSTRING(coord, 1, 2), SUBSTRING(coord, 3, 1), '-', NULL, SUBSTRING(coord, 5, 2) FROM @sample WHERE coord LIKE '[a-z][a-z][0-9]-[a-z][a-z]'
UNION ALL
SELECT SUBSTRING(coord, 1, 2), SUBSTRING(coord, 3, 1), '-', SUBSTRING(coord, 5, 1), NULL FROM @sample WHERE coord LIKE '[a-z][a-z][0-9]-[0-9]'
UNION ALL
SELECT SUBSTRING(coord, 1, 2), SUBSTRING(coord, 3, 1), '-', SUBSTRING(coord, 5, 1), SUBSTRING(coord, 6, 1) FROM @sample WHERE coord LIKE '[a-z][a-z][0-9]-[0-9][a-z]'
UNION ALL
SELECT SUBSTRING(coord, 1, 2), SUBSTRING(coord, 3, 1), '-', SUBSTRING(coord, 5, 1), SUBSTRING(coord, 6, 2) FROM @sample WHERE coord LIKE '[a-z][a-z][0-9]-[0-9][a-z][a-z]'
UNION ALL
SELECT SUBSTRING(coord, 1, 2), SUBSTRING(coord, 3, 1), '-', SUBSTRING(coord, 5, 2), NULL FROM @sample WHERE coord LIKE '[a-z][a-z][0-9]-[0-9][0-9]'
UNION ALL
SELECT SUBSTRING(coord, 1, 2), SUBSTRING(coord, 3, 1), '-', SUBSTRING(coord, 5, 2), SUBSTRING(coord, 7, 1) FROM @sample WHERE coord LIKE '[a-z][a-z][0-9]-[0-9][0-9][a-z]'
UNION ALL
SELECT SUBSTRING(coord, 1, 2), SUBSTRING(coord, 3, 1), '-', SUBSTRING(coord, 5, 2), SUBSTRING(coord, 7, 2) FROM @sample WHERE coord LIKE '[a-z][a-z][0-9]-[0-9][0-9][a-z][a-z]'
UNION ALL
SELECT SUBSTRING(coord, 1, 2), SUBSTRING(coord, 3, 2), '-', NULL, NULL FROM @sample WHERE coord LIKE '[a-z][a-z][0-9][0-9]-'
UNION ALL
SELECT SUBSTRING(coord, 1, 2), SUBSTRING(coord, 3, 2), '-', NULL, SUBSTRING(coord, 6, 1) FROM @sample WHERE coord LIKE '[a-z][a-z][0-9][0-9]-[a-z]'
UNION ALL
SELECT SUBSTRING(coord, 1, 2), SUBSTRING(coord, 3, 2), '-', NULL, SUBSTRING(coord, 6, 2) FROM @sample WHERE coord LIKE '[a-z][a-z][0-9][0-9]-[a-z][a-z]'
UNION ALL
SELECT SUBSTRING(coord, 1, 2), SUBSTRING(coord, 3, 2), '-', SUBSTRING(coord, 6, 1), NULL FROM @sample WHERE coord LIKE '[a-z][a-z][0-9][0-9]-[0-9]'
UNION ALL
SELECT SUBSTRING(coord, 1, 2), SUBSTRING(coord, 3, 2), '-', SUBSTRING(coord, 6, 1), SUBSTRING(coord, 7, 1) FROM @sample WHERE coord LIKE '[a-z][a-z][0-9][0-9]-[0-9][a-z]'
UNION ALL
SELECT SUBSTRING(coord, 1, 2), SUBSTRING(coord, 3, 2), '-', SUBSTRING(coord, 6, 1), SUBSTRING(coord, 7, 2) FROM @sample WHERE coord LIKE '[a-z][a-z][0-9][0-9]-[0-9][a-z][a-z]'
UNION ALL
SELECT SUBSTRING(coord, 1, 2), SUBSTRING(coord, 3, 2), '-', SUBSTRING(coord, 6, 2), NULL FROM @sample WHERE coord LIKE '[a-z][a-z][0-9][0-9]-[0-9][0-9]'
UNION ALL
SELECT SUBSTRING(coord, 1, 2), SUBSTRING(coord, 3, 2), '-', SUBSTRING(coord, 6, 2), SUBSTRING(coord, 8, 1) FROM @sample WHERE coord LIKE '[a-z][a-z][0-9][0-9]-[0-9][0-9][a-z]'
UNION ALL
SELECT SUBSTRING(coord, 1, 2), SUBSTRING(coord, 3, 2), '-', SUBSTRING(coord, 6, 2), SUBSTRING(coord, 8, 2) FROM @sample WHERE coord LIKE '[a-z][a-z][0-9][0-9]-[0-9][0-9][a-z][a-z]'[/code]

Peter Larsson
Helsingborg, Sweden

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2007-01-31 : 10:55:27

Hey Peso,
Yup, that did it. Never gave a thought to UNION each iteration....
Thanks for the help!
(And it's quick, too!)

Andy

There's never enough time to type code right,
but always enough time for a hotfix...

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-01-31 : 11:38:03

Andy -- I want you to promise us that you will use Peso's SQL to move your data from the horrible, non-normalized format and put it into proper related tables. This way, your data is better, your SQL is shorter, and things are quick and easy and efficient. if you need help coming up with a new table design, let us know.

- Jeff
http://weblogs.sqlteam.com/JeffS

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2007-02-01 : 16:22:28

Hey Jeff,
Yeah, Iknow... This is one of those funny kind of things that the vendor got 'almost right'. There are actually 4 bin location columns in the inventory table; BIN_1,BIN_2,BIN_3 and BIN_4. The app vendor was thinking along the lines of what we have (A1-1A,etc..) With each component going into each BIN (BIN_1='A',BIN_2='1', no dash...)
However, all of the app users (not just us; there are a few thousand) have been putting the location into one field as a single string! I promise to do something about it, just as soon as I figure out what...

Andy

There's never enough time to type code right,
but always enough time for a hotfix...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources