PATINDEX
-
I have a field that stores files imported called ImportFile. I need to move the date into a numeric field called ImportDate. The date is between the 2nd and 3rd underscore. I messed with PATINDEX but was unable to figure it out. I believe that is the right way to do this. If there are any PATINDEX experts I'd appreciate the guidance. Thanks.
Other_pg3of7_11202006_Ndc_N.csv
Other_pg4of7_11202006_Ndc_N.csv
Other_pg5of7_11202006_Ndc_N.csv
Other_pg6of7_11202006_Ndc_N.csv
Other_pg7of7_11202006_Ndc_N.csv
Other_pg10of18_11192006_Tan1_1.csv
Other_pg10of23_11192006_Vit7_7.csv
Other_pg11of18_11192006_Tan1_1.csv
Other_pg11of23_11192006_Vit7_7.csv
Other_pg12of18_11192006_Tan1_1.csv
Other_pg12of23_11192006_Vit7_7.csv
-
try something like this:
declare
@tststr varchar(128)
set
@tststr = 'Other_pg3of7_11202006_Ndc_N.csv'
select
patindex('%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%', @tststr),
substring(@tststr, patindex('%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%', @tststr), 8)
-
That works great ... I just realized they send dates weird also. I can get:
11 - 9 instead of 1109
Other_pg12of23_1192006_Vit7_7.csv
or
Jan 1 2007
They send:
112007
Wish they sent
01012007
-
How about something like this then:
declare
@tststr varchar(128)
set
@tststr = 'Other_pg12of23_112007_Vit7_7.csv' --'Other_pg12of23_1192006_Vit7_7.csv' --'Other_pg3of7_11202006_Ndc_N.csv'
select
case when patindex('%_[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]_%', @tststr) > 0
then substring(@tststr, patindex('%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%', @tststr), 8)
when patindex('%_[0-9][0-9][0-9][0-9][0-9][0-9][0-9]_%', @tststr) > 0
then substring(@tststr, patindex('%[0-9][0-9][0-9][0-9][0-9][0-9][0-9]%', @tststr), 7)
when patindex('%_[0-9][0-9][0-9][0-9][0-9][0-9]_%', @tststr) > 0
then substring(@tststr, patindex('%[0-9][0-9][0-9][0-9][0-9][0-9]%', @tststr), 6)
end
-
That works perfect. Interesting how you did it. Thank you!!!
-
You are welcome.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply