Select the length in time of a column

Transact-SQL
1

Hey,

I get results like this

AUDNZD10B10Y_BGN_Curncy AUDNZD10B18M_BGN_Curncy AUDNZD10B1M_BGN_Curncy AUDNZD10B1W_BGN_Curncy AUDNZD10B1Y_BGN_Curncy AUDNZD10B2M_BGN_Curncy

I want to pick out the 10Y, 18M, 1M, 1W, 1Y and 2M here in the select..

I cant find a good way for this.
Anyone know?

2

There can be few ways to do this, one is below (assuming the format is fixed i.e. two underscores and text AUDNZD10B):

DECLARE @STR VARCHAR(100) ='AUDNZD10B10Y_BGN_Curncy'

SELECT REPLACE(PARSENAME(REPLACE(@STR,'_','.'),3),'AUDNZD10B','')

3

This will parse it based on a pattern of a digit followed by D (for Day), M, W or Y. If you don't need D, remove it from the PATINDEX.

SELECT
string,
SUBSTRING(string, location_of_last_digit_of_time_period - time_period_length + 2, time_period_length) AS time_period
FROM (
SELECT 'AUDNZD10B10Y_BGN_Curncy' AS string UNION ALL
SELECT 'AUDNZD10B18M_BGN_Curncy' UNION ALL
SELECT 'AUDNZD10B1M_BGN_Curncy' UNION ALL
SELECT 'AUDNZD10B1W_BGN_Curncy' UNION ALL
SELECT 'AUDNZD10B1Y_BGN_Curncy' UNION ALL
SELECT 'AUDNZD10B2M_BGN_Curncy' UNION ALL
SELECT 'XZILQD43R22B50W12;3lkj;' UNION ALL
SELECT 'I worked for 90days last quarter!'
) AS test_data
CROSS APPLY (
SELECT PATINDEX('%[0-9][DMWY]%', string) AS location_of_last_digit_of_time_period
) AS assign_alias_names1
CROSS APPLY (
SELECT CASE
WHEN SUBSTRING(string, location_of_last_digit_of_time_period - 1, 1) LIKE '[0-9]'
THEN CASE WHEN SUBSTRING(string, location_of_last_digit_of_time_period - 2, 1) LIKE '[0-9]'
THEN 4 ELSE 3 END
ELSE 2 END AS time_period_length
) AS assign_alias_names2

4

If the format is same, you can try something like this too

declare @s varchar(30)
set @s='AUDNZD10B10Y_BGN_Curncy'
select right(replace(@s,'_BGN_Curncy',''),3)