CODEWITHSUNDEEP
mysqlsql
James
James

Reputation: 492

In MySQL, can I UPDATE a value based on a count of certain characters in another column?

I need to update a column in a database with the count of certain characters in another column. Specifically, I need SQL to turn this:

Col1    | Col2
1/2/3   | 0
1/3     | 0
1/2/3/2 | 0
1       | 0

Into this:

Col1    | Col2
1/2/3   | 2
1/3     | 1
1/2/3/2 | 3
1       | 0

Is it possible? Because it seems like it should be possible.

I've been looking around and all I've found is that regular expressions aren't supported in MySQL and I really need to do this directly rather than using another language (so, for example, a simple PHP script to do this won't work for me).

Upvotes: 0

Views: 47

Answers (2)

Mihai
Mihai

Reputation: 26784

UPDATE t SET col2=select (length(col1) - length(replace(col1, '/',''))) FROM t

Upvotes: 2

fancyPants
fancyPants

Reputation: 51888

UPDATE your_table 
SET Col2 = CHAR_LENGTH(Col1) - CHAR_LENGTH(REPLACE(Col1, '/', ''));

Upvotes: 1

Related Questions