Occasionally there may be the need to remove the date from the end of a string. This can be accomplished by using PATINDEX along with SUBSTRING. We will use PATINDEX to find where the date begins and then SUBSTRING to remove the date from the end of the file. We will use ClientName03302010.csv and we want to get ClientName.csv from that to process.
PATINDEX requires an expression to search for and an expression to search from that we will be returned a number, the location of the searched for expression.
We will start by setting the file name equal to a variable:
DECLARE @FileName varchar(50)
SET @FileName = ‘ClientName03302010.csv’
Next we want to find where the date begins by looking for the first number in the string using PATINDEX:
PATINDEX(‘%[0-9]%’, @FileName) – This says that we are going to return the location of the first number that is found, and that it can be any number between and including zero and nine. In this case the return value is 11, because the first number which is zero is the 11th character in the string.
Now that we have the location of the first number we can use SUBSTRING to select only the text. At the same time we will add .csv onto the end and set it as the new value for @FileName:
SET @FileName = SUBSTRING(@FileName,1,PATINDEX(‘%[0-9]%’, @FileName)-1) +’.csv’
This will take the value of @FileName starting from the first character and count 10 spaces (the 11th character minus one character because we want to stop at the character before the number) and add .csv back onto the end of the file.
Below is a copy of the code for you to modify yourself if you would like.
DECLARE @FileName varchar(50)
SET @FileName = ‘ClientName03302010.csv’
SET @FileName = SUBSTRING(@FileName,1,PATINDEX(‘%[0-9]%’, @FileName)-1) +’.csv’
PRINT @FileName
A note: CHARINDEX and PATINDEX are very similar. More on that in another post.
A similar approach can be taken to store the file extension by looking for the period at the end of the string.
