Extract the numeric month/year/week/day value from a string

Question

0.00/5 (No votes)

See more:

I have a drop down to select for Year/Month/Week/Day and the selected values will be assigned to a field as a string . From the generated string I need to extract each values.Let me explain with an example below
@string='30 Years 3 Month 2 Weeks 2 day'.
Output Required: @years=30,@Months=3,@weeks=2,@Days=2

The string can have input in any combinations,
few cases like below
@String='3 Months 20 Days' Or @String='3 Years 2 Weeks' Or @String='10 Days'

So from the available formats I need to extract the @years,@months,@weeks,@days

What I have tried:

Right now I'm out of solutions

Posted 11-Dec-17 1:20am

Prashanth Mundath

Updated 11-Dec-17 20:57pm

Add a Solution

Comments

F-ES Sitecore 11-Dec-17 7:55am

You should store things in an appropriate format and you won't run into these problems. Have a numeric field in your table for years, one for months and so on and store the numbers in the relevant field. That way you don't need to do any string parsing at all.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2017-12-11T02:17:00

Firstly, see the (more than valid) comment from F-ES Sitecore.

However if you really must parse this string then there are several ways to go about it.

You might think of using a loop and using PATINDEX[^] to find the numeric bits e.g. PATINDEX('%[0-9]%',@String) and/or the non-numeric bits PATINDEX('%^[0-9]%',@String). I would not recommend that approach.

My preferred approach...

You could split the string into it's component parts using STRING_SPLIT [^] (from SQL 2016, Google for an alternative solution if you have an earlier version), so you end up with a table like this:

1	30
2	Years
3	3
4	Month
5	2
6	Weeks
7	2
8	day

Then you can do something like this

SQL

;with CTE AS
(
	select ID, part, LEAD(part) OVER (ORDER BY id) AS descriptor
	from @split
)
SELECT '@' + descriptor + '=' + part
FROM CTE
WHERE ID % 2 = 1

which gives you

@Years=30
@Month=3
@Weeks=2
@day=2

But that is not consistent with the descriptors so I would take it a step further and do something like

SQL

declare @descriptors table (part varchar(30), descriptor varchar(30))
insert into @descriptors (part, descriptor) values
('day','@days='),
('days','@days='),
('week','@weeks='),
('weeks','@weeks='),
('month','@month='),
('months','@months='),
('year','@years='),
('years','@years=')

;with CTE2 AS
(
	select ID, part, LEAD(part) OVER (ORDER BY id) AS descrip
	from @split
)
SELECT descriptor + a.part
FROM CTE2 A
INNER JOIN @descriptors B ON A.descrip = B.part
WHERE ID % 2 = 1

which gives

@years=30
@month=3
@weeks=2
@days=2

You'll then want to create a comma-separated list from those values - there are loads of suggestions[^] on that.

which takes me right back to F-ES Sitecore's comment ... your design is not the right way to go about this

Member 7870345 · Answer 2 · 2017-12-11T20:57:00

Here is a function that helps in yoour task:

CREATE FUNCTION  number_that_preceeds 
(
	@input varchar(max),
	@search varchar(max)
)
RETURNS INT
AS
BEGIN
	DECLARE @inputReverse varchar(max)
	DECLARE @searchReverse varchar(max)
	DECLARE @resultStringReverse varchar(max)
	DECLARE @resultString varchar(max)
	DECLARE @string1 varchar(max)
	DECLARE @string2 varchar(max)
	DECLARE @i INT
	DECLARE @oneCharacter CHAR(1)
	DECLARE @result	BIGINT
	DECLARE @position BIGINT

	SET @resultStringReverse=''
	SET @result=0
	SET @inputReverse=REVERSE(UPPER(LTRIM(RTRIM(@input))))
	SET @searchReverse=REVERSE(UPPER(LTRIM(RTRIM(@search))))
	SET @position=CHARINDEX(@searchReverse,@inputReverse)
	IF @position>0
			BEGIN
			/* we need to find the number that comes BEFORE the @search parameter */
			/* so: we reverse both the input string and the search parameter */
			SET @string1=SUBSTRING(@inputReverse,@position+LEN(@searchReverse),LEN(@inputReverse)-@position+1-LEN(@searchReverse))
			/* now in @string1 we have just the reverse of the substring that preceeds @search */
			SET @string2=LTRIM(RTRIM(@string1))
			/* in resultStringReverse we concat just the numeric part of string2*/
			SET @resultStringReverse=''
			SET @i=1
			WHILE @i<=LEN(@string2)
				BEGIN
				SET @oneCharacter=SUBSTRING(@string2,@i,1)
				IF CHARINDEX(@oneCharacter,'0123456789')=0
					SET @i=LEN(@string2)+9999
				ELSE
					BEGIN
					SET @resultStringReverse=@resultStringReverse+@onecharacter
					SET @i=@i+1
					END
				END
			END

	
	SET @resultString=REVERSE(@resultStringReverse)
	/* with another reverse we have in resultString the just the string that contains the number that preceeds @searech*/
	IF @resultString=''
		SET @result='0'
	SET @result=CONVERT(int,@resultString)
	--SET @resultString=@resultStringReverse+'<'+STR(@result)+'>'
	RETURN @result

END
GO

and then you can use:

SQL

SELECT DBO.number_that_preceeds('3 YEAR 5 DAYS','DAY')

to obtein
5
or

SQL

SELECT DBO.number_that_preceeds('3 YEAR 5 DAYS','Month')

to obtein
0

Extract the numeric month/year/week/day value from a string

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0