CODEWITHSUNDEEP
sql-server
user2819684
user2819684

Reputation:

Extracting month using substring in sql server

I have the following code 

@FiscalMonth  As varchar(MAX) ='[Dim Date].[Fiscal].[Fiscal Month Number].&[10]&[2014]&[4]&[1]'

In my case the month will be 10 . I have tried the following code 

SET @FiscalMonthNumber = cast(substring(right(@FiscalMonth,18),1,2) as nvarchar(max))

The code above works fine but when I have 

@FiscalMonth  As varchar(MAX) ='[Dim Date].[Fiscal].[Fiscal Month Number].&[10]&[2014]&[11]&[1]'

I have as result 0] which is incorrect .

Upvotes: 0

Views: 933

Answers (3)

M.Ali
M.Ali

Reputation: 69524

Or you can extract all the numbers from you string and then extract the month number from your number string. something like this

DECLARE @FiscalMonth1 As varchar(MAX) ='[Dim Date].[Fiscal].[Fiscal Month Number].&[10]&[2014]&[4]&[1]'

WHILE PATINDEX('%[^0-9]%',@FiscalMonth1) <> 0

SET @FiscalMonth1 = STUFF(@FiscalMonth1,PATINDEX('%[^0-9]%',@FiscalMonth1),1,'')

SELECT LEFT(@FiscalMonth1, 2)

Upvotes: 0

M.Ali
M.Ali

Reputation: 69524

I have checked your code it is working fine it is showing 10 in both cases maybe you need to look into you logic.

DECLARE @FiscalMonth varchar(MAX) ='[Dim Date].[Fiscal].[Fiscal Month Number].&[10]&[2014]&[4]&[1]'

DECLARE @FiscalMonthNumber nvarchar(max) = cast(substring(right(@FiscalMonth,18),1,2) as nvarchar(max))

SELECT @FiscalMonthNumber

DECLARE @FiscalMonth1 As varchar(MAX) ='[Dim Date].[Fiscal].[Fiscal Month Number].&[10]&[2014]&[4]&[1]'

DECLARE @FiscalMonthNumber1 AS nvarchar(max) = cast(substring(right(@FiscalMonth,18),1,2) as nvarchar(max))

SELECT @FiscalMonthNumber1

Upvotes: 0

anon
anon

Reputation:

Ugh. This assumes that the first time you encounter a pattern like '[n' (where n is any number, and it will occur within the first 8000 characters of the string), that is the month:

DECLARE @fm VARCHAR(MAX);
SET @fm = '[Dim Date].[Fiscal].[Fiscal Month Number].&[10]&[2014]&[4]&[1]';

SELECT SUBSTRING(@fm, PATINDEX('%[[][0-9]%', @fm) + 1, 
  CHARINDEX(']', SUBSTRING(@fm, PATINDEX('%[[][0-9]%', @fm) + 1, 8000)) - 1);

Result:

----------
10

If there are other possible patterns or edge cases, make them known.

SQLfiddle demo

Upvotes: 2

Related Questions