I am new in SQL and need a small help in solving this. I have a column which has a file path name "C:\Users\akumar\Desktop_Claim Files\AMSO_CLAIMS_YYYYMMDD.txt".

And, I need to create a column (Dataset)with data of file name without a date.

Here are the following examples of my data

"C:\Users\akumar\Desktop_Claim Files\AMSO_CLAIMS_YYYYMMDD.txt" - > New Column (Dataset) value is AMSO_Claims

""C:\Users\akumar\Desktop_Claim Files\V2_HC_ADV_CLAIMS_YYYYMMDD.txt" -> New column (Dataset) value will be V2_HC_ADV_CLAIMS

""C:\Users\akumar\Desktop_Claim Files\V2_MEDICA_PHA_CLAIMS_YYYYMMDD.txt" -> New column (Dataset) value will be V2_MEDICA_PHA_CLAIMS


Is this what you're looking for?

Declare @T Table (Filename varchar(100))
insert into @T(Filename) values
('C:\Users\akumar\Desktop_Claim Files\AMSO_CLAIMS_YYYYMMDD.txt'),
('C:\Users\akumar\Desktop_Claim Files\V2_HC_ADV_CLAIMS_YYYYMMDD.txt'),
('C:\Users\akumar\Desktop_Claim Files\V2_MEDICA_PHA_CLAIMS_YYYYMMDD.txt')

SELECT Filename
    ,NewColumn = replace(substring(filename, 1, len(filename) - charindex('_', REVERSE(filename))), 'C:\Users\akumar\Desktop_Claim Files\', '')
    ,NewColumnYYYYMMDD = reverse(substring(reverse(filename),1,charindex('_',REVERSE(filename))-1))

| Filename                                                              | NewColumn            | NewColumnYYYYMMDD |
| C:\Users\akumar\Desktop_Claim Files\AMSO_CLAIMS_YYYYMMDD.txt          | AMSO_CLAIMS          | YYYYMMDD.txt      |
| C:\Users\akumar\Desktop_Claim Files\V2_HC_ADV_CLAIMS_YYYYMMDD.txt     | V2_HC_ADV_CLAIMS     | YYYYMMDD.txt      |
| C:\Users\akumar\Desktop_Claim Files\V2_MEDICA_PHA_CLAIMS_YYYYMMDD.txt | V2_MEDICA_PHA_CLAIMS | YYYYMMDD.txt      |
  • 1
    Yes, Now I also want to add a new column with the YYYYMMDD in the same table – Avinash Kumar Mar 19 '19 at 18:09
  • @AvinashKumar - I updated the example for the YYYYMMDD column. – Scott Hodgin Mar 19 '19 at 18:24
  • Thank you so much for your help. – Avinash Kumar Mar 19 '19 at 19:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.