May 23, 2018 at 11:50 am
I am trying to extract either a 2 digit or 3 digit number to the right in a product description.
Here is the current query
SELECT WORK_ORDER.BASE_ID, WORK_ORDER.PART_ID, PART.DESCRIPTION
FROM WORK_ORDER INNER JOIN
PART ON WORK_ORDER.PART_ID = PART.ID
WHERE (WORK_ORDER.BASE_ID LIKE N'18%') AND (PART.DESCRIPTION LIKE N'%CNT%')
The results
LOVIDIA 30 CNT/BT, REGULAR
LOVIDIA 30 CNT/BT, REGULAR
GLP1 SUPPORT 60 CNT/BT
DEVROM CHEWABLE 100 CNT BT
ZENDOCRINE 60 CNT/BT, CAPS
US DIGESTZEN, US 100 CNT/BT, TB
US DIGESTZEN, US 100 CNT/BT, TB
US DIGESTZEN, US 100 CNT/BT, TB
US DIGESTZEN, US 100 CNT/BT, TB
PROTANDIM NRF2 (US) 30 CNT. BOTTLE
PROTANDIM NRF2 (US) 30 CNT. BOTTLE
PROTANDIM NRF2 (US) 30 CNT. BOTTLE
PROTANDIM NRF2 (US) 30 CNT. BOTTLE
PROTANDIM NRF2 (US) 30 CNT. BOTTLE
PROTANDIM NRF2 (US) 30 CNT. BOTTLE
PROTANDIM NRF2 (US) 30 CNT. BOTTLE
I tried a few I found but the statements would get confused if there was a number before what I wanted like in the GLP1 SUPPORT line it would just give me 1 instead of 60.
Any help is greatly appreciated as I have hit a wall trying different options. Thank you.
May 23, 2018 at 12:11 pm
Mark_S_DLI - Wednesday, May 23, 2018 11:50 AMI am trying to extract either a 2 digit or 3 digit number to the right in a product description.Here is the current query
SELECT WORK_ORDER.BASE_ID, WORK_ORDER.PART_ID, PART.DESCRIPTION
FROM WORK_ORDER INNER JOIN
PART ON WORK_ORDER.PART_ID = PART.ID
WHERE (WORK_ORDER.BASE_ID LIKE N'18%') AND (PART.DESCRIPTION LIKE N'%CNT%')The results
LOVIDIA 30 CNT/BT, REGULAR
LOVIDIA 30 CNT/BT, REGULAR
GLP1 SUPPORT 60 CNT/BT
DEVROM CHEWABLE 100 CNT BT
ZENDOCRINE 60 CNT/BT, CAPS
US DIGESTZEN, US 100 CNT/BT, TB
US DIGESTZEN, US 100 CNT/BT, TB
US DIGESTZEN, US 100 CNT/BT, TB
US DIGESTZEN, US 100 CNT/BT, TB
PROTANDIM NRF2 (US) 30 CNT. BOTTLE
PROTANDIM NRF2 (US) 30 CNT. BOTTLE
PROTANDIM NRF2 (US) 30 CNT. BOTTLE
PROTANDIM NRF2 (US) 30 CNT. BOTTLE
PROTANDIM NRF2 (US) 30 CNT. BOTTLE
PROTANDIM NRF2 (US) 30 CNT. BOTTLE
PROTANDIM NRF2 (US) 30 CNT. BOTTLEI tried a few I found but the statements would get confused if there was a number before what I wanted like in the GLP1 SUPPORT line it would just give me 1 instead of 60.
Any help is greatly appreciated as I have hit a wall trying different options. Thank you.
Not really sure what you are looking for based just on your post. It would help if you would post the DDL (CREATE TABLE statement) for the tables involved, some sample data for each of the tables (INSERT INTO statements), and what you expect to have returned by the query based on the sample data you post.
I have an idea, but need something to work with to really know.
May 23, 2018 at 12:17 pm
Sorry.. I should have put an example of what I was looking for.
From this : GLP1 SUPPORT 60 CNT/BT I want the number 60 only.
60 Count bottle.. or 100 count bottle, etc.
[DESCRIPTION] [nvarchar](120) NULL
May 23, 2018 at 12:21 pm
Mark_S_DLI - Wednesday, May 23, 2018 12:17 PMSorry.. I should have put an example of what I was looking for.From this : GLP1 SUPPORT 60 CNT/BT I want the number 60 only.
60 Count bottle.. or 100 count bottle, etc.
[DESCRIPTION] [nvarchar](120) NULL
Again, read what I posted earlier. How am I supposed to setup a sandbox environment, write code and test code, and provide you an answer with what you have provided.
Please remember, we are volunteers giving of our own time to help people like you. To do this we need your help.
May 23, 2018 at 1:03 pm
Mark_S_DLI - Wednesday, May 23, 2018 12:17 PMSorry.. I should have put an example of what I was looking for.From this : GLP1 SUPPORT 60 CNT/BT I want the number 60 only.
60 Count bottle.. or 100 count bottle, etc.
[DESCRIPTION] [nvarchar](120) NULL
Click that first link right under Lynn's signature.
It really helps when we can create the same table and have some sample data. Its all in that link
Sue
May 23, 2018 at 1:27 pm
The data I am looking for to be returned from a select statement is the 2 or 3 digit number to the right on the text ' CNT'
I hope this is what you were looking for and after reviewing the post Forum Etiquette: How to post data/code on a forum to get the best help.
Thank you.
--===== Create the test table with
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PART](
[ROWID] [int] IDENTITY(1,1) NOT NULL,
[ID] [nvarchar](30) NOT NULL,
[DESCRIPTION] [nvarchar](120) NULL
CONSTRAINT [PK_PART] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT PART ON
--===== Insert the test data into the test table
INSERT INTO PART
(ID, DESCRIPTION)
SELECT 'AMB-02','LOVIDIA 30 CNT/BT, REGULAR', UNION ALL
SELECT 'AMB-03','GLP1 SUPPORT 60 CNT/BT', UNION ALL
SELECT 'DEV-02','DEVROM CHEWABLE 100 CNT BT', UNION ALL
SELECT 'DOT-01','ZENDOCRINE 60 CNT/BT, CAPS', UNION ALL
SELECT 'DOT-04','US DIGESTZEN, US 100 CNT/BT, TB', UNION ALL
SELECT 'LIF-06','PROTANDIM NRF2 (US) 30 CNT. BOTTLE', UNION ALL
SELECT 'LIF-07','PETANDIM FOR DOGS, 30 CNT BT (US)', UNION ALL
SELECT 'LIF-09','PROTANDIM NRF2 30 CNT (JP)', UNION ALL
SELECT 'LIF-10','PROTANDIM HK 30 CNT. BOTTLE (HK CHI)', UNION ALL
SELECT 'LIF-12','PROTANDIM CA 30 CNT. BTL (CA ENG/FRE)', UNION ALL
SELECT 'LIF-13','PETANDIM FOR DOGS, 30 CNT BT (JP)', UNION ALL
SELECT 'LIF-22','PROTANDIM NRF1 JP 60 CNT. BT', UNION ALL
SELECT 'LIF-23','PROTANDIM NRF2 TH 30 CNT. BT', UNION ALL
SELECT 'LIF-25','PHYSIQ FAT BURN JP 120 CNT. BT', UNION ALL
SELECT 'LIF-26','PROTANDIM NRF1 TH 60 CNT. BT', UNION ALL
SELECT 'LIF-27','PROTANDIM NRF1 EU 60 CNT. BT', UNION ALL
SELECT 'LIF-29','PROTANDIM NRF2 MX 30 CNT. BT', UNION ALL
SELECT 'LIF-30','PROTANDIM NRF1 CA 60 CNT BT ', UNION ALL
SELECT 'LIF-31','PROTANDIM NRF1 MX 60 CNT. BT', UNION ALL
SELECT 'LIF-32','PROTANDIM NRF1 60 CNT US ENG, BT ', UNION ALL
SELECT 'LIF-33','PROTANDIM NRF1 60 CNT HK ENG, BT ', UNION ALL
SELECT 'LIF-34','PROTANDIM NRF2 TW 30 CNT. BT', UNION ALL
SELECT 'NUS-08','BOTANAGAR 120 CNT/BT (TAIWAN)', UNION ALL
SELECT 'NUS102','VITOX 180 CNT/BT (JAPAN), CAPSULE', UNION ALL
SELECT 'NUS120','TeGREEN 30 CNT/ BT (JAPAN), CAPS(KENKO)', UNION ALL
SELECT 'NUS141','METABOTRIM DIET (JP) 60 CNT/BT , CAPS', UNION ALL
SELECT 'NUS145','SEASONAL CARE 90 CNT/BT (JP), CAPS', UNION ALL
SELECT 'NUS169','DETOX FORMULA 60 CNT/BT (US), CAPSULE', UNION ALL
SELECT 'NUS170','TEGREEN 120 CNT/BT (SG)', UNION ALL
SELECT 'NUS172','BIO GINKGO 60 CNT/BT (MY)', UNION ALL
SELECT 'NUS184','DIGESTIVE FORMULA 30 CNT (TW) BT', UNION ALL
SELECT 'NUS217','FLEX FORMULA 120 CNT/BT (SG)', UNION ALL
SELECT 'NUS218','TEGREEN 30 CNT/BT (INDONESIA)', UNION ALL
SELECT 'NUS221','PROBIO PCC (KO) 30 CNT/BT', UNION ALL
SELECT 'NUS222','PROBIO PCC (TW) 30 CNT/BT', UNION ALL
SELECT 'NUS224','PROBIO PCC (JP) 30 CNT/BT', UNION ALL
SELECT 'NUS225','PROBIO PCC (SG) 30 CNT/BT ', UNION ALL
SELECT 'NUS227','REISHIMAX 60 CNT/BT (MY) CAPSULE', UNION ALL
SELECT 'NUS229','BIO GINKGO REFORMULATED 60 CNT/BT', UNION ALL
SELECT 'NUS238','PROBIO PCC (HK) 30 CNT/BT ', UNION ALL
SELECT 'NUS246','REISHI EX 60 CNT/BT (JP), CAPSULE', UNION ALL
SELECT 'NUS259','BIO GINKGO 27/7 60 CNT/BT (HK)', UNION ALL
SELECT 'NUS260','BONE FORMULA (US) 180 CNT/BT', UNION ALL
SELECT 'NUS265','UNFG COMPLEX F (EU) 120 CNT BTL', UNION ALL
SELECT 'NUS266','ESTERA PHASE II TRANSITION (US) 30CNT/BT', UNION ALL
SELECT 'NUS267','ESTERA PHASE III MAINTENANCE (US) 60 CNT', UNION ALL
SELECT 'NUS275','TeGREEN 30 CNT/ BT(MX), CAPSULES', UNION ALL
SELECT 'NUS276','GENLOC TR90 FIT JAPAN 90 CNT BOTTLE ', UNION ALL
SELECT 'NUS279','PROBIO 30 CNT/ BT (US), CAPSULES', UNION ALL
SELECT 'NUS280','CORTITROL 60 CNT/ BT (US), CAPSULES', UNION ALL
SELECT 'NUS290','UNFG REISHIMAX 60 CNT/BT (EU), CAPS', UNION ALL
SELECT 'NUS292','DIGESTIVE FORMULA (US) 60 CNT/BT', UNION ALL
SELECT 'NUS293','ESTERA CRANBERRY 60 CNT/BT (US)', UNION ALL
SELECT 'NUS303','UNFG BONE FORMULA (EU) 180 CNT/BT', UNION ALL
SELECT 'NUS313','PROBIO UNFG 30 CNT/ BT (EU), CAPSULES', UNION ALL
SELECT 'NUS318','CORTITROL (HK 2017) 60 CNT/BT ', UNION ALL
SELECT 'NUS319','UNFG JUNGAMALS 90 CNT/BT (HK)', UNION ALL
SELECT 'NUS-41','LIFEPAK TEEN 120 CNT/BT, US', UNION ALL
SELECT 'PYC-08','PBGS+ 60 CNT/BT', UNION ALL
SELECT 'PYC-09','PBGS+ 120 CNT/BT', UNION ALL
SELECT 'PYC-11','PBGS+ 120 CNT/BT (KOREA)', UNION ALL
SELECT 'PYC-12','PBGS+ 60 CNT/BT (MEXICO)', UNION ALL
SELECT 'SHA-28','RITESTART KIDS & TEENS 120 CNT/BT', UNION ALL
SELECT 'SHK-11','HERB-LAX 240 CNT TAB US (KOSHER)', UNION ALL
SELECT 'SHK-12','HERB-LAX 60 CNT TAB US (KOSHER)', UNION ALL
SELECT 'SHK-14','HERB-LAX 240 CNT TAB CAN', UNION ALL
SELECT 'SHK-21','PHYTOCOL-ST TB,120 CNT BT, MY ', UNION ALL
SELECT 'SIM-01','BABY GUM RELIEF TB 135CNT', UNION ALL
SELECT 'TWI-20','RESVITAL RESVERATROL 500MG 60CNT', UNION ALL
SELECT 'TWI-21','RESERVEAGE KERATIN BOOSTER W BIOTIN 60CNT', UNION ALL
SELECT 'TWI-24','RESERVEAGE TRES BEAUTY 3 90CNT', UNION ALL
SELECT 'TWI-25','RESVITAL BEAUTY 3 90CNT', UNION ALL
SELECT 'TWI-27','RESERVEAGE BEAUTIFUL LEGS W/DIOSMIN 30CNT', UNION ALL
SELECT 'TWI-29','RESERVEAGE PROSTATE SCIENCE 60CNT', UNION ALL
SELECT 'TWI-30','RESERVEAGE KERATIN BOOSTER W/BIOTIN FOR MEN 60CNT', UNION ALL
SELECT 'TWI-47','RSV BEAUTIFULORA 60CNT', UNION ALL
SELECT 'YOU-07','ESSENTIALZYME 90CNT BI-LAYER TAB. BOTTLE'
--===== Set the identity insert back to normal
SET IDENTITY_INSERT PART OFF
May 23, 2018 at 2:33 pm
Here is my take on your request:
--===== Create the test table with
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID('[dbo].[PART]','U') IS NOT NULL
DROP TABLE [dbo].[PART];
GO
CREATE TABLE [dbo].[PART](
[ROWID] [int] IDENTITY(1,1) NOT NULL,
[ID] [nvarchar](30) NOT NULL,
[DESCRIPTION] [nvarchar](120) NULL
CONSTRAINT [PK_PART] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
--===== Insert the test data into the test table
INSERT INTO [dbo].[PART]
(ID, DESCRIPTION)
SELECT 'AMB-02','LOVIDIA 30 CNT/BT, REGULAR' UNION ALL
SELECT 'AMB-03','GLP1 SUPPORT 60 CNT/BT' UNION ALL
SELECT 'DEV-02','DEVROM CHEWABLE 100 CNT BT' UNION ALL
SELECT 'DOT-01','ZENDOCRINE 60 CNT/BT, CAPS' UNION ALL
SELECT 'DOT-04','US DIGESTZEN, US 100 CNT/BT, TB' UNION ALL
SELECT 'LIF-06','PROTANDIM NRF2 (US) 30 CNT. BOTTLE' UNION ALL
SELECT 'LIF-07','PETANDIM FOR DOGS, 30 CNT BT (US)' UNION ALL
SELECT 'LIF-09','PROTANDIM NRF2 30 CNT (JP)' UNION ALL
SELECT 'LIF-10','PROTANDIM HK 30 CNT. BOTTLE (HK CHI)' UNION ALL
SELECT 'LIF-12','PROTANDIM CA 30 CNT. BTL (CA ENG/FRE)' UNION ALL
SELECT 'LIF-13','PETANDIM FOR DOGS, 30 CNT BT (JP)' UNION ALL
SELECT 'LIF-22','PROTANDIM NRF1 JP 60 CNT. BT' UNION ALL
SELECT 'LIF-23','PROTANDIM NRF2 TH 30 CNT. BT' UNION ALL
SELECT 'LIF-25','PHYSIQ FAT BURN JP 120 CNT. BT' UNION ALL
SELECT 'LIF-26','PROTANDIM NRF1 TH 60 CNT. BT' UNION ALL
SELECT 'LIF-27','PROTANDIM NRF1 EU 60 CNT. BT' UNION ALL
SELECT 'LIF-29','PROTANDIM NRF2 MX 30 CNT. BT' UNION ALL
SELECT 'LIF-30','PROTANDIM NRF1 CA 60 CNT BT ' UNION ALL
SELECT 'LIF-31','PROTANDIM NRF1 MX 60 CNT. BT' UNION ALL
SELECT 'LIF-32','PROTANDIM NRF1 60 CNT US ENG, BT ' UNION ALL
SELECT 'LIF-33','PROTANDIM NRF1 60 CNT HK ENG, BT ' UNION ALL
SELECT 'LIF-34','PROTANDIM NRF2 TW 30 CNT. BT' UNION ALL
SELECT 'NUS-08','BOTANAGAR 120 CNT/BT (TAIWAN)' UNION ALL
SELECT 'NUS102','VITOX 180 CNT/BT (JAPAN), CAPSULE' UNION ALL
SELECT 'NUS120','TeGREEN 30 CNT/ BT (JAPAN), CAPS(KENKO)' UNION ALL
SELECT 'NUS141','METABOTRIM DIET (JP) 60 CNT/BT , CAPS' UNION ALL
SELECT 'NUS145','SEASONAL CARE 90 CNT/BT (JP), CAPS' UNION ALL
SELECT 'NUS169','DETOX FORMULA 60 CNT/BT (US), CAPSULE' UNION ALL
SELECT 'NUS170','TEGREEN 120 CNT/BT (SG)' UNION ALL
SELECT 'NUS172','BIO GINKGO 60 CNT/BT (MY)' UNION ALL
SELECT 'NUS184','DIGESTIVE FORMULA 30 CNT (TW) BT' UNION ALL
SELECT 'NUS217','FLEX FORMULA 120 CNT/BT (SG)' UNION ALL
SELECT 'NUS218','TEGREEN 30 CNT/BT (INDONESIA)' UNION ALL
SELECT 'NUS221','PROBIO PCC (KO) 30 CNT/BT' UNION ALL
SELECT 'NUS222','PROBIO PCC (TW) 30 CNT/BT' UNION ALL
SELECT 'NUS224','PROBIO PCC (JP) 30 CNT/BT' UNION ALL
SELECT 'NUS225','PROBIO PCC (SG) 30 CNT/BT ' UNION ALL
SELECT 'NUS227','REISHIMAX 60 CNT/BT (MY) CAPSULE' UNION ALL
SELECT 'NUS229','BIO GINKGO REFORMULATED 60 CNT/BT' UNION ALL
SELECT 'NUS238','PROBIO PCC (HK) 30 CNT/BT ' UNION ALL
SELECT 'NUS246','REISHI EX 60 CNT/BT (JP), CAPSULE' UNION ALL
SELECT 'NUS259','BIO GINKGO 27/7 60 CNT/BT (HK)' UNION ALL
SELECT 'NUS260','BONE FORMULA (US) 180 CNT/BT' UNION ALL
SELECT 'NUS265','UNFG COMPLEX F (EU) 120 CNT BTL' UNION ALL
SELECT 'NUS266','ESTERA PHASE II TRANSITION (US) 30CNT/BT' UNION ALL
SELECT 'NUS267','ESTERA PHASE III MAINTENANCE (US) 60 CNT' UNION ALL
SELECT 'NUS275','TeGREEN 30 CNT/ BT(MX), CAPSULES' UNION ALL
SELECT 'NUS276','GENLOC TR90 FIT JAPAN 90 CNT BOTTLE ' UNION ALL
SELECT 'NUS279','PROBIO 30 CNT/ BT (US), CAPSULES' UNION ALL
SELECT 'NUS280','CORTITROL 60 CNT/ BT (US), CAPSULES' UNION ALL
SELECT 'NUS290','UNFG REISHIMAX 60 CNT/BT (EU), CAPS' UNION ALL
SELECT 'NUS292','DIGESTIVE FORMULA (US) 60 CNT/BT' UNION ALL
SELECT 'NUS293','ESTERA CRANBERRY 60 CNT/BT (US)' UNION ALL
SELECT 'NUS303','UNFG BONE FORMULA (EU) 180 CNT/BT' UNION ALL
SELECT 'NUS313','PROBIO UNFG 30 CNT/ BT (EU), CAPSULES' UNION ALL
SELECT 'NUS318','CORTITROL (HK 2017) 60 CNT/BT ' UNION ALL
SELECT 'NUS319','UNFG JUNGAMALS 90 CNT/BT (HK)' UNION ALL
SELECT 'NUS-41','LIFEPAK TEEN 120 CNT/BT, US' UNION ALL
SELECT 'PYC-08','PBGS+ 60 CNT/BT' UNION ALL
SELECT 'PYC-09','PBGS+ 120 CNT/BT' UNION ALL
SELECT 'PYC-11','PBGS+ 120 CNT/BT (KOREA)' UNION ALL
SELECT 'PYC-12','PBGS+ 60 CNT/BT (MEXICO)' UNION ALL
SELECT 'SHA-28','RITESTART KIDS & TEENS 120 CNT/BT' UNION ALL
SELECT 'SHK-11','HERB-LAX 240 CNT TAB US (KOSHER)' UNION ALL
SELECT 'SHK-12','HERB-LAX 60 CNT TAB US (KOSHER)' UNION ALL
SELECT 'SHK-14','HERB-LAX 240 CNT TAB CAN' UNION ALL
SELECT 'SHK-21','PHYTOCOL-ST TB,120 CNT BT, MY ' UNION ALL
SELECT 'SIM-01','BABY GUM RELIEF TB 135CNT' UNION ALL
SELECT 'TWI-20','RESVITAL RESVERATROL 500MG 60CNT' UNION ALL
SELECT 'TWI-21','RESERVEAGE KERATIN BOOSTER W BIOTIN 60CNT' UNION ALL
SELECT 'TWI-24','RESERVEAGE TRES BEAUTY 3 90CNT' UNION ALL
SELECT 'TWI-25','RESVITAL BEAUTY 3 90CNT' UNION ALL
SELECT 'TWI-27','RESERVEAGE BEAUTIFUL LEGS W/DIOSMIN 30CNT' UNION ALL
SELECT 'TWI-29','RESERVEAGE PROSTATE SCIENCE 60CNT' UNION ALL
SELECT 'TWI-30','RESERVEAGE KERATIN BOOSTER W/BIOTIN FOR MEN 60CNT' UNION ALL
SELECT 'TWI-47','RSV BEAUTIFULORA 60CNT' UNION ALL
SELECT 'YOU-07','ESSENTIALZYME 90CNT BI-LAYER TAB. BOTTLE';
GO
SELECT
[p].*
,[Qty] = RIGHT(RTRIM(SUBSTRING([p].[DESCRIPTION],1,PATINDEX('%CNT%', [p].[DESCRIPTION]) - 1))
,PATINDEX('%[^0-9]%',LTRIM(REVERSE(SUBSTRING([p].[DESCRIPTION],1,PATINDEX('%CNT%', [p].[DESCRIPTION]) - 1)))) - 1)
FROM
[dbo].[PART] AS [p]
GO
May 23, 2018 at 2:45 pm
I am getting an error.
Msg 537, Level 16, State 3, Line 1
Invalid length parameter passed to the LEFT or SUBSTRING function.
I tried playing with it but not seeing the error.. I am so so at doing the SQL stuff.
May 23, 2018 at 2:57 pm
Mark_S_DLI - Wednesday, May 23, 2018 2:45 PMI am getting an error.Msg 537, Level 16, State 3, Line 1
Invalid length parameter passed to the LEFT or SUBSTRING function.I tried playing with it but not seeing the error.. I am so so at doing the SQL stuff.
No idea. I can copy/paste the code from my post and it runs with no issues.
Please post the code you are running that has issues.
May 23, 2018 at 2:58 pm
Mark_S_DLI - Wednesday, May 23, 2018 2:45 PMI am getting an error.Msg 537, Level 16, State 3, Line 1
Invalid length parameter passed to the LEFT or SUBSTRING function.I tried playing with it but not seeing the error.. I am so so at doing the SQL stuff.
Make sure you copy and pasted it correctly. I just ran it and it was good. Double check to make sure the create table and sample data were correct.
Lynn made some changes as the script with the create table and data (thanks very much for providing that) needed a few tweaks. We can explain those later though. Try to get what Lynn posted working first as he really knows SQL. So it's good for that DDL and data that was posted.
You may want to run Lynn's entire script in a test databases. Then you can check for any differences.
Sue
May 23, 2018 at 3:27 pm
Looking at the forum you posted in I have to ask, are you actually using SQL Server 2000 or older?
May 23, 2018 at 3:29 pm
So I figured out not all DESCRIPTION fields have CNT in them so it's returning a null value. I did a filter description like '%CNT%' then I was able to run the query.
May 23, 2018 at 3:31 pm
I fixed it this way
SELECT ROWID, ID, DESCRIPTION, CASE WHEN DESCRIPTION LIKE '%CNT%' THEN RIGHT(RTRIM(SUBSTRING(DESCRIPTION, 1, PATINDEX('%CNT%', DESCRIPTION) - 1)), PATINDEX('%[^0-9]%',
LTRIM(REVERSE(SUBSTRING(DESCRIPTION, 1, PATINDEX('%CNT%', DESCRIPTION) - 1)))) - 1) ELSE NULL END AS Qty
FROM PART
May 23, 2018 at 3:32 pm
SQL 2012 R2
It is an ERP product that has come up through the years.
May 23, 2018 at 3:41 pm
Mark_S_DLI - Wednesday, May 23, 2018 3:32 PMSQL 2012 R2It is an ERP product that has come up through the years.
Here is a better way to do what you used that UNION ALL to insert test data:
INSERT INTO [dbo].[PART](ID, DESCRIPTION)
VALUES
('AMB-02','LOVIDIA 30 CNT/BT, REGULAR')
,('AMB-03','GLP1 SUPPORT 60 CNT/BT')
,('DEV-02','DEVROM CHEWABLE 100 CNT BT')
,('DOT-01','ZENDOCRINE 60 CNT/BT, CAPS')
,('DOT-04','US DIGESTZEN, US 100 CNT/BT, TB')
,('LIF-06','PROTANDIM NRF2 (US) 30 CNT. BOTTLE')
,('LIF-07','PETANDIM FOR DOGS, 30 CNT BT (US)')
,('LIF-09','PROTANDIM NRF2 30 CNT (JP)')
,('LIF-10','PROTANDIM HK 30 CNT. BOTTLE (HK CHI)')
,('LIF-12','PROTANDIM CA 30 CNT. BTL (CA ENG/FRE)')
,('LIF-13','PETANDIM FOR DOGS, 30 CNT BT (JP)')
,('LIF-22','PROTANDIM NRF1 JP 60 CNT. BT')
,('LIF-23','PROTANDIM NRF2 TH 30 CNT. BT')
,('LIF-25','PHYSIQ FAT BURN JP 120 CNT. BT')
,('LIF-26','PROTANDIM NRF1 TH 60 CNT. BT')
,('LIF-27','PROTANDIM NRF1 EU 60 CNT. BT')
,('LIF-29','PROTANDIM NRF2 MX 30 CNT. BT')
,('LIF-30','PROTANDIM NRF1 CA 60 CNT BT ')
,('LIF-31','PROTANDIM NRF1 MX 60 CNT. BT')
,('LIF-32','PROTANDIM NRF1 60 CNT US ENG, BT ')
,('LIF-33','PROTANDIM NRF1 60 CNT HK ENG, BT ')
,('LIF-34','PROTANDIM NRF2 TW 30 CNT. BT')
,('NUS-08','BOTANAGAR 120 CNT/BT (TAIWAN)')
,('NUS102','VITOX 180 CNT/BT (JAPAN), CAPSULE')
,('NUS120','TeGREEN 30 CNT/ BT (JAPAN), CAPS(KENKO)')
,('NUS141','METABOTRIM DIET (JP) 60 CNT/BT , CAPS')
,('NUS145','SEASONAL CARE 90 CNT/BT (JP), CAPS')
,('NUS169','DETOX FORMULA 60 CNT/BT (US), CAPSULE')
,('NUS170','TEGREEN 120 CNT/BT (SG)')
,('NUS172','BIO GINKGO 60 CNT/BT (MY)')
,('NUS184','DIGESTIVE FORMULA 30 CNT (TW) BT')
,('NUS217','FLEX FORMULA 120 CNT/BT (SG)')
,('NUS218','TEGREEN 30 CNT/BT (INDONESIA)')
,('NUS221','PROBIO PCC (KO) 30 CNT/BT')
,('NUS222','PROBIO PCC (TW) 30 CNT/BT')
,('NUS224','PROBIO PCC (JP) 30 CNT/BT')
,('NUS225','PROBIO PCC (SG) 30 CNT/BT ')
,('NUS227','REISHIMAX 60 CNT/BT (MY) CAPSULE')
,('NUS229','BIO GINKGO REFORMULATED 60 CNT/BT')
,('NUS238','PROBIO PCC (HK) 30 CNT/BT ')
,('NUS246','REISHI EX 60 CNT/BT (JP), CAPSULE')
,('NUS259','BIO GINKGO 27/7 60 CNT/BT (HK)')
,('NUS260','BONE FORMULA (US) 180 CNT/BT')
,('NUS265','UNFG COMPLEX F (EU) 120 CNT BTL')
,('NUS266','ESTERA PHASE II TRANSITION (US) 30CNT/BT')
,('NUS267','ESTERA PHASE III MAINTENANCE (US) 60 CNT')
,('NUS275','TeGREEN 30 CNT/ BT(MX), CAPSULES')
,('NUS276','GENLOC TR90 FIT JAPAN 90 CNT BOTTLE ')
,('NUS279','PROBIO 30 CNT/ BT (US), CAPSULES')
,('NUS280','CORTITROL 60 CNT/ BT (US), CAPSULES')
,('NUS290','UNFG REISHIMAX 60 CNT/BT (EU), CAPS')
,('NUS292','DIGESTIVE FORMULA (US) 60 CNT/BT')
,('NUS293','ESTERA CRANBERRY 60 CNT/BT (US)')
,('NUS303','UNFG BONE FORMULA (EU) 180 CNT/BT')
,('NUS313','PROBIO UNFG 30 CNT/ BT (EU), CAPSULES')
,('NUS318','CORTITROL (HK 2017) 60 CNT/BT ')
,('NUS319','UNFG JUNGAMALS 90 CNT/BT (HK)')
,('NUS-41','LIFEPAK TEEN 120 CNT/BT, US')
,('PYC-08','PBGS+ 60 CNT/BT')
,('PYC-09','PBGS+ 120 CNT/BT')
,('PYC-11','PBGS+ 120 CNT/BT (KOREA)')
,('PYC-12','PBGS+ 60 CNT/BT (MEXICO)')
,('SHA-28','RITESTART KIDS & TEENS 120 CNT/BT')
,('SHK-11','HERB-LAX 240 CNT TAB US (KOSHER)')
,('SHK-12','HERB-LAX 60 CNT TAB US (KOSHER)')
,('SHK-14','HERB-LAX 240 CNT TAB CAN')
,('SHK-21','PHYTOCOL-ST TB,120 CNT BT, MY ')
,('SIM-01','BABY GUM RELIEF TB 135CNT')
,('TWI-20','RESVITAL RESVERATROL 500MG 60CNT')
,('TWI-21','RESERVEAGE KERATIN BOOSTER W BIOTIN 60CNT')
,('TWI-24','RESERVEAGE TRES BEAUTY 3 90CNT')
,('TWI-25','RESVITAL BEAUTY 3 90CNT')
,('TWI-27','RESERVEAGE BEAUTIFUL LEGS W/DIOSMIN 30CNT')
,('TWI-29','RESERVEAGE PROSTATE SCIENCE 60CNT')
,('TWI-30','RESERVEAGE KERATIN BOOSTER W/BIOTIN FOR MEN 60CNT')
,('TWI-47','RSV BEAUTIFULORA 60CNT')
,('YOU-07','ESSENTIALZYME 90CNT BI-LAYER TAB. BOTTLE');
GO
Look up table value constructor.
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply