Extracting substring from one coloumn and placing it into another SQL

Question

I have a table like so

[filenameAndDate][DateCreated]

the first column looks like this "myvideo/12.12.2012" and the second column is empty

How would I write a sql query to extract the date from [filenameAndDate] and place it into the [DateCreated] column

What database are you using? – D'Arcy Rittich May 21 '15 at 01:55 — D'Arcy Rittich, May 21 '15 at 01:55
sorry thought I added that, database is MYSQL – tama May 21 '15 at 01:56 — tama, May 21 '15 at 01:56

score 2 · Accepted Answer · answered May 21 '15 at 02:04

2

UPDATE [dbo].[FileNames]
SET [DateCreated] = Convert(Date, 
   SUBSTRING(FileNameAndDate, CHARINDEX('/', FileNameAndDate, 0) + 1,
   LEN(fileNameAndDate)))

this is for MSSQL

answered May 21 '15 at 02:04

JohnW

326
4
9

score 1 · Answer 2 · answered May 21 '15 at 02:13

You can try this, mate:

UPDATE
  <your_table>
SET
  DateCreated = RIGHT(filenameAndDate, 10)
WHERE
  filenameAndDate = 'myvideo/12.12.2012';

Suggestion:

Maybe you can organize your table in a way it may not hurt an application based on the content of a field.
Another one is the format of the date you'll be using, it would be better if you use the yyyy-mm-dd format.

PS: this is for MySQL

Cheers!

score 0 · Answer 3 · edited May 23 '17 at 12:07

0

Equivalent of explode() to work with strings in MySQL

then use it like

insert into blabla (col1, col2) value (val1, SPLIT_STRING(val1,'/',2));

edited May 23 '17 at 12:07

Community

1
1

answered May 21 '15 at 01:55

Ghean

21
7

Extracting substring from one coloumn and placing it into another SQL

3 Answers3