CODEWITHSUNDEEP
sqlsql-server
LeedsWalker
LeedsWalker

Reputation: 199

Strip a date from varchar field in SQL

I have a varchar field such as 'Created by blogs 16/10/2014 #123456 more stuff'.

I have stripped out the characters e.g. stuff(Col, 1, patindex('%[0-9]%', Col)-1, '') and also replaced the #, so that I am left with 16/10/2016 123456.

I now want to remove the date. The date could be positioned before or after the 123456. The date may be in various formats.

My end goal is to be left with 123456.

Upvotes: 0

Views: 92

Answers (2)

mindbdev
mindbdev

Reputation: 444

Try this

DECLARE @YourString VARCHAR(100)
SET @YourString = 'Created by blogs 16/10/2014 #123456 more stuff'

SELECT LEFT(RIGHT(@YourString,PATINDEX('%[0-9]%',@YourString)-1),CHARINDEX(' ',RIGHT(@YourString,PATINDEX('%[0-9]%',@YourString)-1)))

Upvotes: 0

JohnS
JohnS

Reputation: 2052

Try this. It assumes that the number is prefixed with '#' and followed by a space (or is last).:

DECLARE @Str VARCHAR(100)
SET @Str = 'Created by blogs 16/10/2014 #123456 more stuff'

SELECT
  SUBSTRING(
            SUBSTRING(@Str, CHARINDEX('#', @Str) + 1, 999), 
            1, 
            CHARINDEX(' ', 
                      SUBSTRING(@Str + ' ', 
                                CHARINDEX('#', @Str) + 1, 999)))

Replace @Str with your column name. Added a bugfix for when the number is last

Upvotes: 1

Related Questions