CODEWITHSUNDEEP
mysqlsql
tama
tama

Reputation: 315

Extracting substring from one coloumn and placing it into another SQL

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

Upvotes: 0

Views: 34

Answers (3)

Zymon Castaneda
Zymon Castaneda

Reputation: 759

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!

Upvotes: 1

JohnW
JohnW

Reputation: 336

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

this is for MSSQL

Upvotes: 2

Ghean
Ghean

Reputation: 21

Equivalent of explode() to work with strings in MySQL

then use it like 

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

Upvotes: 0

Related Questions