MS SQL - Extract number to the right of text

Question

MS SQL - Extract number to the right of text

Viewing 7 posts - 16 through 21 (of 21 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Mark_S_DLI - Wednesday, May 23, 2018 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.

Well, that explains why it failed. Glad you were able to fix it.

Mark_S_DLI SSC Enthusiast Points: 170 More actions · Answer 2

Mark_S_DLI

SSC Enthusiast

Points: 170

May 23, 2018 at 3:45 pm

#1991720

Thanks for the help, it's appreciated.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Here is a rewrite of the code taking into account "CNT" may not be in the description. Give it a try.
--===== 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)
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

SELECT
[p].*
,[Qty] = RIGHT(LEFT(RTRIM([p].[DESCRIPTION]),LEN(RTRIM([p].[DESCRIPTION])) - (PATINDEX('%[0-9]%',LTRIM(REVERSE([p].[DESCRIPTION]))) - 1))
,(PATINDEX('%[^0-9]%',REVERSE(LEFT(RTRIM([p].[DESCRIPTION]), LEN(RTRIM([p].[DESCRIPTION])) - (PATINDEX('%[0-9]%',LTRIM(REVERSE([p].[DESCRIPTION]))) - 1)))) - 1))
FROM
[dbo].[PART] AS [p]
GO

Mark_S_DLI SSC Enthusiast Points: 170 More actions · Answer 4

I get an error due to one of the current descriptions.. Also like 79 which has C4 shows 4 but no CNT so its value should not show up.

These two rows

,('TWI-50','RSV BEAUTIFULORA C4')
,('TWI-51','5 - LOXIN (BOSWELLIA EXT)')

USE tempdb --===== 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) 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 A') ,('TWI-48','RSV BEAUTIFULORA B') ,('TWI-49','RSV BEAUTIFULORA C') ,('TWI-50','RSV BEAUTIFULORA C4') ,('TWI-51','5 - LOXIN (BOSWELLIA EXT)') ,('YOU-07','ESSENTIALZYME 90CNT BI-LAYER TAB. BOTTLE'); GO

SELECT [p].* ,[Qty] = RIGHT(LEFT(RTRIM([p].[DESCRIPTION]),LEN(RTRIM([p].[DESCRIPTION])) - (PATINDEX('%[0-9]%',LTRIM(REVERSE([p].[DESCRIPTION]))) - 1)) ,(PATINDEX('%[^0-9]%',REVERSE(LEFT(RTRIM([p].[DESCRIPTION]), LEN(RTRIM([p].[DESCRIPTION])) - (PATINDEX('%[0-9]%',LTRIM(REVERSE([p].[DESCRIPTION]))) - 1)))) - 1)) FROM [dbo].[PART] AS [p] GO

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Mark_S_DLI - Wednesday, May 23, 2018 4:38 PM
I get an error due to one of the current descriptions.. Also like 79 which has C4 shows 4 but no CNT so its value should not show up.
These two rows
,('TWI-50','RSV BEAUTIFULORA C4')
,('TWI-51','5 - LOXIN (BOSWELLIA EXT)')
USE tempdb --===== 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) 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 A') ,('TWI-48','RSV BEAUTIFULORA B') ,('TWI-49','RSV BEAUTIFULORA C') ,('TWI-50','RSV BEAUTIFULORA C4') ,('TWI-51','5 - LOXIN (BOSWELLIA EXT)') ,('YOU-07','ESSENTIALZYME 90CNT BI-LAYER TAB. BOTTLE'); GO
SELECT [p].* ,[Qty] = RIGHT(LEFT(RTRIM([p].[DESCRIPTION]),LEN(RTRIM([p].[DESCRIPTION])) - (PATINDEX('%[0-9]%',LTRIM(REVERSE([p].[DESCRIPTION]))) - 1)) ,(PATINDEX('%[^0-9]%',REVERSE(LEFT(RTRIM([p].[DESCRIPTION]), LEN(RTRIM([p].[DESCRIPTION])) - (PATINDEX('%[0-9]%',LTRIM(REVERSE([p].[DESCRIPTION]))) - 1)))) - 1)) FROM [dbo].[PART] AS [p] GO

Here is what I will say. When you provide sample data, it really needs to be representative of your data so we don't run into these types of issues. It is these edge cases that we need to know about so we can account for them. Are there any other data issues that could affect the output?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

So, should this also not return a value: ,('TWI-51','5 - LOXIN (BOSWELLIA EXT)')?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

But then you have these type of issues: RESVITAL BEAUTY 3 90CNT, RESVITAL RESVERATROL 500MG 60CNT; when trying another way to isolate a numeric value causes another anomaly when trying to process strings like this.

I guess at this point I stop working on this and you use the best alternative for your situation. There may be others ways to skin this cat but I don't have access to your system so not sure if there is a way to split out other parts easily leaving the count portion easier to extract.