Hot answers tagged

9

First here is sample data mysql> drop table if exists mytable; Query OK, 0 rows affected (0.03 sec) mysql> create table mytable -> ( -> id int not null, -> value VARCHAR(255), -> primary key (id) -> ); Query OK, 0 rows affected (0.06 sec) mysql> insert into mytable (id) values (1),(2),(3); Query OK, ...


7

If you are guaranteed to only ever use the 26 letters of the US English alphabet (both upper-case and lower-case versions) then sure, you can get away with using LIKE and/or PATINDEX with the simple range notation of [a-z] But, if you might get characters not found in the en-US alphabet yet available in various Code Pages / Collations for VARCHAR data (e.g. ...


4

I've got a post here that does something similar. Basically I'm using a recursive CTE to go loop over and over again replacing one "bad" character at a time. I'm using STUFF to strip 1 character (although you can use it to replace with a space) and PATINDEX to find the location of the character I want to remove. You could modify it slightly to do what you ...


4

You need to use the information_schema database to generate the script. Collect all columns from every table of every database that have the following criteria: Exclude the following databases: information_schema performance_schema mysql COLUMN_TYPE values with one of the following characteristics: starts with CHAR( starts with VARCHAR( ends with ...


3

Assuming that you don't want to load the data from the CSV file into a database table and then do a correlated UPDATE, UPDATE mytable t SET value = (SELECT value FROM tbl_with_csv_data csv WHERE csv.primary_key = t.primary_key) WHERE EXISTS( SELECT 1 FROM tbl_with_csv_data csv WHERE ...


3

SELECT SUBSTRING(col, 1, CHAR_LENGTH(col) - CHAR_LENGTH( SUBSTRING_INDEX(col, '.com', -1) ) ) ; If you want update the strings from a table, chopping off what is on the right of .com: UPDATE tableX SET col = SUBSTRING(col, 1, CHAR_LENGTH(col) - CHAR_LENGTH( SUBSTRING_INDEX(col, '.com', -1) ) ) WHERE SUBSTRING_INDEX(col, '.com', -1) <&...


3

As you found, you have to use '' to represent a single quote ('). You do not need to use the SPACE() function: select replace(character_column, '''', '') from your_table Note: Using SPACE() could actually create an issue as it returns VARCHAR(4000).


3

Thanks everyone, but I'm going to use these FUCNTIONs that I made. One FUNCTION to extract letters and one to extract numbers (even decimals). Letters: USE `test`; DROP function IF EXISTS `alpha`; DELIMITER $$ USE `test`$$ CREATE DEFINER=`root`@`localhost` FUNCTION `alpha`( vStr CHAR(75)) RETURNS char(32) CHARSET latin1 BEGIN DECLARE vInd, vLen ...


3

I have a rather ugly approach that will strip alphanumeric characters from a user variable STRIPPING ALPHAS SET @st='r1+o2l-3a4*n5d6o7'; SELECT (@st:=REPLACE(@st,ch,'')) FROM ( SELECT (x*10+y) ndx,SUBSTR(chars,x*10+y,1) ch FROM (SELECT 'abcdefghijklmnopqrstuvwxyz' chars) L, (SELECT 0 x UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 ...


3

Why? Because Postgres replace() is a standard SQL function that works the same as in other RDBMS. Example: replace() in SQL Server: Replaces all occurrences of a specified string value with another string value. While regexp_replace() is used to ... Replace substring(s) matching a POSIX regular expression. The handling of regular expressions is ...


2

This may come across as silly, but if the CMS ignores subsequent <!--more--> tags, you shouldn't really care if it adds one after every </em> closing tag. Sure it may make the posts slightly larger than they should be, but since they're just going to be ignored, it seems silly to spend two days trying to not do that when at the end of the day it ...


1

The doubling up is because of your nested parenthesis. Processing will start with the inner-most pair and work outwards. In this case the innermost replacement is 1 -> Apple meaning 11 -> Apple1 -> AppleApple. Reverse the nesting, starting with the longest source value and work outward. Better still, put the mappings in a separate table and join to ...


1

You should avoid using triggers in conjunction with REPLACE INTO on multiple rows. Why ? REPLACE INTO is nothing more than a mechanical DELETE and INSERT. It can incite mysqld to address deadlocks (See my answer to How I prevent deadlock occurrence in my application?) Here are two comments that shows the LIFO approach to processing triggers Posted by ...


1

You can't do this in a single step. The locking required to truncate the table precludes you querying the table at the same time. The best option you would have is to declare a global temporary table (DGTT) and insert the rows you want into it, truncate the source table, and then insert the rows from the DGTT back into the source table. Something like: ...


1

If you are doing SQL, then you probably would need to do a TRUNCATE/DELETE on the table and then INSERT into. If you are using LOAD and/or IMPORT, they both have options for clearing the table before getting data into the table.


1

Disable any FKs pointing to the table Create table SAVE as SELECT * FROM A Truncate table A Begin to create a script with the following with xfer as (select cast ('a' as text) as targ_tab, cast ('b' as text) as from_tab) select xfer.*, tabs.table_name as targ_table_name, cols.table_name as from_table_name, 'alter table ' || tabs.table_name || ' add (' ||...


1

Use the REPLACE function, e.g. select replace(yourfield,'''','') from yourtable where ... Here we are replacing a single quote character with an empty string. Since SQL character literals are themselves enclosed in single quotes, we escape the single quote character in the literal by doubling it. More information about the use of the REPLACE function ...


1

Look carefully at the query's output Query OK, 0 rows affected (0.00 sec) Rows matched: 1 Changed: 0 Warnings: 0 The expression Rows matched is based on WHERE bug_id=452;. That matched. When you executed it, the change evidently failed because the short_desc column didn't change because short_desc does not equal 'I don\'t know why my previous comment ...


1

Perhaps this UPDATE may do it: UPDATE tablename SET `time` = `time` - INTERVAL 2 YEAR;


1

Here is the output of the NOW() function mysql> select now(); +---------------------+ | now() | +---------------------+ | 2012-04-24 22:07:47 | +---------------------+ 1 row in set (0.03 sec) mysql> This is the format to enter mydate. For a double, just append .0 to any integer. No need for numbers to to have quotes: REPLACE into ...



Only top voted, non community-wiki answers of a minimum length are eligible