Determining Distinct Values Within Unique Strings

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

Transact-SQL (2008)

Determining Distinct Values Within Unique Strings

Author

Topic

awmeyertimmy
Starting Member

3 Posts

Posted - 2015-04-23 : 12:52:42

I am trying to create a piece of code in sql server 2008 that will grab specific values from each distinct string within my dbo table. The ultimate goal is to make a drop down box within Visual Studio so that one can choose all lines from the database that contain a specific product code (see definition of product code below). Example strings:

in_0314_95pf_500_w_0315

in_0314_500_95pf_0315_w

The part of these strings I am wishing to identify is the 3 digit numeric code (in this case let us call it product code) that appears once within each string. There are roughly 300 different product codes.

The problem is that these product code values do not appear in the same position within each unique string. Hence, I am having a hard time determining the product code because I can't use substring, charindex, like, etc.

Any ideas? Any help is MUCH appreciated.

Thanks, Adam

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2015-04-23 : 13:43:19

Which is the 3 character product code that you are trying to pick up in the examples you posted? Assuming it is 500, and assuming it is always preceded by and followed by an underscore, this:

SELECT SUBSTRING(Col, PATINDEX('%[_][0-9][0-9][0-9][_]%','_'+Col+'_') , 3) FROM TABLE

If it is preceded by and followed by any non-numeric character, then this:

SELECT SUBSTRING(Col, PATINDEX('%[^0-9][0-9][0-9][0-9][^0-9]%','_'+Col+'_'), 3) FROM TABLE

awmeyertimmy
Starting Member

3 Posts

Posted - 2015-04-24 : 09:41:13

500 is the product code in each of my examples. At first, I was under the impression that the product code was always followed and proceeded by an underscore (example: _500_). However, I have come across a few strings where the product code appears in the string like this in_0614_95PF500_0315 or in_0614_500PF_0315. So, it is always EITHER followed by or proceeded by an underscore but not always both. Is there a way to solve for this? Thanks! -a

awmeyertimmy
Starting Member

3 Posts

Posted - 2015-04-24 : 09:42:12

Note: 500 is the product code in all examples I used (my original post and my 2nd reply post).

Ifor
Aged Yak Warrior

700 Posts

Posted - 2015-04-24 : 10:22:11

[code]
-- *** Test Data ***
CREATE TABLE #t
(
YourString varchar(25) NOT NULL
);
INSERT INTO #t
VALUES ('in_0314_95pf_500_w_0315'), ('in_0314_500_95pf_0315_w');
-- *** End Test Data ***

SELECT YourString
,SUBSTRING(YourString, PATINDEX('%[^0-9][0-9][0-9][0-9][^0-9]%', YourString) + 1, 3) AS ProductCode
FROM #t;
[/code]

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2015-04-24 : 10:33:29

quote:
Originally posted by awmeyertimmy

500 is the product code in each of my examples. At first, I was under the impression that the product code was always followed and proceeded by an underscore (example: _500_). However, I have come across a few strings where the product code appears in the string like this in_0614_95PF500_0315 or in_0614_500PF_0315. So, it is always EITHER followed by or proceeded by an underscore but not always both. Is there a way to solve for this? Thanks! -a

Yes, the second query I posted will work correctly in all cases as long as you have one and only one 3-digit number preceded and followed by something that is not a number. So it will work in all the following cases:

1. in_0314_95pf_500_w_0315 <-- your example
2. 500Win_0314_95pf_w_0315 <-- product code is at the beginning
3. in_0314_95pf_5X0_w_X500 <-- product code is at the end
4. in_0314_95pfA500Aw_0315 <-- product code has letters next to it.

One case where it will give you the wrong results is if there is no product code at all. For example:
5. in_0314_95pf_ABC_w_0315
If that is a possibility add a NULLIF function to the expression like shown below:

SUBSTRING(Col, NULLIF(PATINDEX('%[^0-9][0-9][0-9][0-9][^0-9]%','_'+Col+'_'),0), 3)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources