5 by 5 number separately - 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

General SQL Server Forums

New to SQL Server Programming

5 by 5 number separately

Author

Topic

sslow
Starting Member

6 Posts

Posted - 2014-10-02 : 04:54:10

Hi All,

I would like to know how to separate 5 by 5 number.

For Example 51428-65821

I would like to separate the last 5 digits number into 658 and 21 separately.

May i know how was the sql code looks like ?

Hope you can reply me ASAP

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2014-10-02 : 07:26:08

DECLARE @V1 varchar(11) = '51428-65821'
DECLARE @P1 varchar(3) = SUBSTRING(@V1,7,3)
DECLARE @P2 varchar(2) = RIGHT(@V1,2)

SELECT @P1
SELECT @P2

sslow
Starting Member

6 Posts

Posted - 2014-10-02 : 20:07:15

DECLARE T1.PART_NUMBER varchar(11) = '[0-9][0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9]'
DECLARE T1.Prefix varchar(3) = SUBSTRING(T1.PART_NUMBER,7,3)
DECLARE T1.ID varchar(2) = RIGHT(T1.PART_NUMBER,2)

SELECT T1.ORGANIZATION_CODE, T1.PART_NUMBER, T1.Prefix, T1.ID, T1.ITEM_TYPE_NAME, T1.INVENTORY_ITEM_STATUS_CODE AS ITEM_STATUS, T1.DESCRIPTION, T1.ER_CODE, T2.DWSF_DEPT_WSF, T2.IBO_INV_BUSINESS_OWNER, T2.PH_BUSINESS_GROUP + '.' + T2.PH_BUSINESS_UNIT + '.' + T2.PH_PRODUCT_LINE + '.' + T2.PH_REVENUE_MU + '.' + T2.PH_SUB_FAMILY AS PHHC
FROM vMTL_SYSTEM_ITEMS T1
JOIN vMTL_ITEM_CATEGORIES_ALL_XTAB T2 ON T1.PART_NUMBER = T2.PART_NUMBER AND T1.ORGANIZATION_CODE = T2.ORGANIZATION_CODE
WHERE T1.ORGANIZATION_CODE IN ('IM1')
AND T1.ER_CODE IN ('2863','09','21','MODS')
AND T1.ITEM_TYPE_NAME NOT IN ('PTO Model','PTO Option Class','PTO Kit','PTO Kit Config Only','FG BOM Config Only','ATP Lead-Time Item','Service PTO Model','Service Int Config Only','Planning Item')
AND T1.PART_NUMBER LIKE '[0-9][0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9]'

but i get any error of Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '.'.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2014-10-27 : 09:36:44

DECLARE T1.PART_NUMBER should be DECLARE @PART_NUMBER

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources