November 20, 2009 at 11:22 am
Hi Everyone, I'm trying to figure out if there is a way to have SSRS pull the first word from a text box.
Currently the text box looks something like this: "2/2/2009 Adjustment made..."
I'm hoping to be able to extract the date from this text... I tried the CONVERT(VARCHAR(8)... function but that doesn't work since the user will enter the date in whatever fashion they please (eg 02/02/2009 etc.). Any ideas out there on how to achieve this?
Thank you in advance.
November 20, 2009 at 1:02 pm
This may set your thinking onto the correct track
DECLARE @Something VARCHAR(max)
SET @Something = '2/2/2009 Adjustment made...'
SELECT SUBSTRING(@Something,1,CHARINDEX(' ', LTRIM(@Something))-1) AS 'Date'
Which will return 2/2/2009 - now the weakness of what I have above.
If the text does NOT have a blank after the date, then of course it fails.
Also the date itself must not contain any blanks such as 2 -10-09.
And perhaps not the last problem the date must at the beginning of the text field.
So all I can hope for is that this stirs your creative juices and you arrive at a solution.
Corrected spelling mistake 3:32 PM
3:44 PM Added LTRIM so that blanks which might precede the date are removed
November 20, 2009 at 2:31 pm
Been fooling with this ever since my last post and this may be more complicated than it need be but it will handle input such as
1. 'ab 2/2/2009 Adjustment made...'
2. ' 2/2/2009 Adjustment made...'
3. '2/2/2009 Adjustment made...'
DECLARE @Something VARCHAR(max)
DECLARE @Num INT
DECLARE @Pos INT
SET @Num =1
SET @Pos = 1
SET @Something = 'ab 2/2/2009 Adjustment made...'
WHILE @Num < LEN(@Something)
BEGIN
IF ISNUMERIC(SUBSTRING(@Something,@Num,1)) = 1
BREAK
ELSE
SET @Num = @num + 1
CONTINUE
END
SET @Pos = LEN(@Something) - @Num
SELECT SUBSTRING(@Something,@Num,CHARINDEX(' ', SUBSTRING(@Something,@Num,@Pos))-1) AS 'Date'
Now this will be slow going if you have thousands of rows to handle, but again
I hope it will get your creative juices flowing, so you can come up with a quicker method.
November 20, 2009 at 2:40 pm
Thank you so much! This points me in the right direction!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply