Two sets of numbers, one 6 in LEN, the other 7..need to match the first 6 digits in both as a query?

Question

Two sets of numbers, one 6 in LEN, the other 7..need to match the first 6 digits in both as a query?

chef423

SSCertifiable

Points: 5279
More actions
March 27, 2017 at 9:02 am

#341898

I have two SKU's, example:
LEN=7: 7956616
LEN=6: 795661
OR
LEN=11: 13827502064
LEN=10: 1382750206
So I need to be able to search to find numbers that match their first x amount of numbers...
Something like this:
SELECT *FROM MyTableWHERE Mycolumn LIKE '%[0-9][0-9][0-9][0-9][0-9][0-9]%'
I need this to work to insert pricing too..so if I SKU of LEN=7 has a price, I need to be able to update that same price in the LEN=6 of a SKU with the first same 6 digits.
Thank you for any nudge.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99129 More actions · Answer 1

Is this along the lines of what you're looking for?
DECLARE @Sku VARCHAR(7), @NewPrice DECIMAL(8,2); --Set your parameter values

UPDATE Product SET Price = @NewPrice WHERE SKU = @Sku OR (SKU = LEFT(@Sku, Len(@Sku-1)) AND LEN(Sku) = len(@Sku) - 1);

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 2

Slightly different method
😎

USE TEEST; GO SET NOCOUNT ON;

DECLARE @NUMLIST TABLE ( NM_ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED ,SKU BIGINT NOT NULL ); INSERT INTO @NUMLIST(SKU) VALUES (7856614),(7956615),(7956616),(7756616),(795661);

DECLARE @NUMDIG INT = 6;

SELECT NL.NM_ID ,NL.SKU ,NL.SKU / POWER(10,CEILING(LOG10(NL.SKU)) - @NUMDIG) AS MATCH_DIGIT ,DENSE_RANK() OVER ( ORDER BY NL.SKU / POWER(10,CEILING(LOG10(NL.SKU)) - @NUMDIG) ) AS MATCH_GROUP FROM @NUMLIST NL;

Output
NM_ID SKU MATCH_DIGIT MATCH_GROUP ----------- -------------------- -------------------- -------------------- 4 7756616 775661 1 1 7856614 785661 2 2 7956615 795661 3 3 7956616 795661 3 5 795661 795661 3

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

Here are my suggestions depending on the data type.
DECLARE @NUMLIST TABLE ( NM_ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED ,SKU BIGINT NOT NULL ); INSERT INTO @NUMLIST(SKU) VALUES (7856614),(7956615),(7956616),(7756616),(795661);

DECLARE @NUMDIG INT = 6;

SELECT long.NM_ID, long.SKU, ISNULL(short.SKU, long.SKU) AS matching FROM @NUMLIST long LEFT JOIN @NUMLIST short ON (long.SKU / 10 = short.SKU AND LEN( long.sku) = @NUMDIG + 1) GO

DECLARE @NUMLIST TABLE ( NM_ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED ,SKU varchar(15) NOT NULL ); INSERT INTO @NUMLIST(SKU) VALUES (7856614),(7956615),(7956616),(7756616),(795661);

DECLARE @NUMDIG INT = 6;

SELECT long.NM_ID, long.SKU, ISNULL(short.SKU, long.SKU) AS matching FROM @NUMLIST long LEFT JOIN @NUMLIST short ON (long.SKU LIKE short.SKU + '_' AND LEN( long.sku) = @NUMDIG + 1)

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

chef423 SSCertifiable Points: 5279 More actions · Answer 4

Luis Cazares - Monday, March 27, 2017 9:57 AM
Here are my suggestions depending on the data type.
DECLARE @NUMLIST TABLE ( NM_ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED ,SKU BIGINT NOT NULL ); INSERT INTO @NUMLIST(SKU) VALUES (7856614),(7956615),(7956616),(7756616),(795661); DECLARE @NUMDIG INT = 6; SELECT long.NM_ID, long.SKU, ISNULL(short.SKU, long.SKU) AS matching FROM @NUMLIST long LEFT JOIN @NUMLIST short ON (long.SKU / 10 = short.SKU AND LEN( long.sku) = @NUMDIG + 1) GO DECLARE @NUMLIST TABLE ( NM_ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED ,SKU varchar(15) NOT NULL ); INSERT INTO @NUMLIST(SKU) VALUES (7856614),(7956615),(7956616),(7756616),(795661); DECLARE @NUMDIG INT = 6;
SELECT long.NM_ID, long.SKU, ISNULL(short.SKU, long.SKU) AS matching FROM @NUMLIST long LEFT JOIN @NUMLIST short ON (long.SKU LIKE short.SKU + '_' AND LEN( long.sku) = @NUMDIG + 1)

I will only be comparing nvarchar(20) which is mainly INT data, I only care about INT's up to 12 in LEN

Luis Cazares SSC Guru Points: 183706 More actions · Answer 5

chef423 - Monday, March 27, 2017 12:31 PM
I will only be comparing nvarchar(20) which is mainly INT data, I only care about INT's up to 12 in LEN

But I hope that you understand why did I post 2 options.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

chef423 SSCertifiable Points: 5279 More actions · Answer 6

Luis Cazares - Monday, March 27, 2017 1:15 PM
chef423 - Monday, March 27, 2017 12:31 PM
I will only be comparing nvarchar(20) which is mainly INT data, I only care about INT's up to 12 in LEN
But I hope that you understand why did I post 2 options.

I do, but I need this to work, dynamically with any set of numbers, from 12 digits to 6 digits....out DB has 53,000 SKU's

Select * from Inventory where ItemNum ...

You have some INT hard coded....maybe I need to look it over again. ?

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 7

A quick test harness and a set based method to play around with
😎

USE TEEST; GO SET NOCOUNT ON;

DECLARE @NUM_ROWS INT = 100000;

;WITH T(N) AS (SELECT X.N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) X(N)) , NUMS(N) AS (SELECT TOP(@NUM_ROWS) ROW_NUMBER() OVER (ORDER BY @@VERSION) FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7,T T8,T T9) ,SKU_LIST AS ( SELECT NM.N ,ABS(CHECKSUM(NEWID())) / POWER(10,(ABS(CHECKSUM(NEWID())) % 3)) AS SKU FROM NUMS NM ) ,SKU_STRING AS ( SELECT SL.N AS SKU_ID ,CONVERT(VARCHAR(20),SL.SKU,0) AS SKU ,LEN(CONVERT(VARCHAR(20),SL.SKU,0)) AS SKU_LEN ,MIN(LEN(CONVERT(VARCHAR(20),SL.SKU,0))) OVER (PARTITION BY @@VERSION) AS MIN_LEN ,MAX(LEN(CONVERT(VARCHAR(20),SL.SKU,0))) OVER (PARTITION BY @@VERSION) AS MAX_LEN FROM SKU_LIST SL ) SELECT SS.SKU_ID ,SS.SKU ,SS.SKU_LEN ,SS.MIN_LEN ,SS.MAX_LEN ,DENSE_RANK() OVER (ORDER BY CASE WHEN SS.SKU_LEN >= 2 THEN LEFT(SS.SKU, 2) ELSE NULL END ASC) AS SKU_GRP_02 ,DENSE_RANK() OVER (ORDER BY CASE WHEN SS.SKU_LEN >= 3 THEN LEFT(SS.SKU, 3) ELSE NULL END ASC) AS SKU_GRP_03 ,DENSE_RANK() OVER (ORDER BY CASE WHEN SS.SKU_LEN >= 4 THEN LEFT(SS.SKU, 4) ELSE NULL END ASC) AS SKU_GRP_04 ,DENSE_RANK() OVER (ORDER BY CASE WHEN SS.SKU_LEN >= 5 THEN LEFT(SS.SKU, 5) ELSE NULL END ASC) AS SKU_GRP_05 ,DENSE_RANK() OVER (ORDER BY CASE WHEN SS.SKU_LEN >= 6 THEN LEFT(SS.SKU, 6) ELSE NULL END ASC) AS SKU_GRP_06 ,DENSE_RANK() OVER (ORDER BY CASE WHEN SS.SKU_LEN >= 7 THEN LEFT(SS.SKU, 7) ELSE NULL END ASC) AS SKU_GRP_07 ,DENSE_RANK() OVER (ORDER BY CASE WHEN SS.SKU_LEN >= 8 THEN LEFT(SS.SKU, 8) ELSE NULL END ASC) AS SKU_GRP_08 ,DENSE_RANK() OVER (ORDER BY CASE WHEN SS.SKU_LEN >= 9 THEN LEFT(SS.SKU, 9) ELSE NULL END ASC) AS SKU_GRP_09 ,DENSE_RANK() OVER (ORDER BY CASE WHEN SS.SKU_LEN >= 10 THEN LEFT(SS.SKU,10) ELSE NULL END ASC) AS SKU_GRP_10 FROM SKU_STRING SS;