Login or Register to Ask a Question and Join Our Community

Sponsored Content
Full Discussion: How to delete a data starting with a phrase in a table - SQL?
Special Forums UNIX and Linux Applications How to delete a data starting with a phrase in a table - SQL? Post 302973382 by baris35 on Tuesday 17th of May 2016 10:29:36 AM
Old 05-17-2016
How to delete a data starting with a phrase in a table - SQL?
Hello,
I am trying to remove some rows in a table, which are including a phrase at a defined column but i could not find the unique result for this.

What I wish to do is to remove all lines including http://xx.yy at link column

Code:
+----+----------+-----------+----------+--------------------------------------------------------+---------+
| id |   userid | statusid  |   name   | link                                                 	| notes   | 
+----+----------+-----------+----------+--------------------------------------------------------+---------+
|  1 |        1 |         1 | sql-TEST | ["http:\/\/google.com","http:\/\/xx.aa.bb"]		| test1	  | 
|  2 |        1 |         1 | sql-TEST | ["http:\/\/xx.yy.zz","http:\/\/google.fr"]		| test2   | 
|  3 |        1 |         2 | sql-TEST | ["http:\/\/xx.aa.bb","http:\/\/xx.aa.bb"]		| test3   | 
|  4 |        1 |         2 | sql-TEST | ["http:\/\/nbc.aa.bb","http:\/\/xx.yy.bb"]		| test4   | 
|  5 |        1 |         2 | sql-TEST | ["http:\/\/tt.aa.bb","http:\/\/xx.aa.bb"]		| test5   | 
|  6 |        1 |         2 | sql-TEST | ["http:\/\/xx.aa.bb","http:\/\/xx.yy.bb"]		| test6   | 
|  7 |        1 |         2 | sql-TEST | ["http:\/\/www.cnn.com"]				| test7   | 
+----+----------+-----------+----------+--------------------------------------------------------+---------+


Table name is mytable,

I tried below command but no joy:

Code:
delete from mytable where link like '%["http:\/\/xx.yy%' ;

Maybe as datas in related cells were seperated by "," it is not functioning..

Expected result should be:

Code:
+----+----------+-----------+----------+--------------------------------------------------------+---------+
| id |   userid | statusid  |   name   | link                                                 	| notes   | 
+----+----------+-----------+----------+--------------------------------------------------------+---------+
|  1 |        1 |         1 | sql-TEST | ["http:\/\/google.com","http:\/\/xx.aa.bb"]		| test1	  | 
|  2 |        1 |         1 | sql-TEST | ["http:\/\/google.fr"]					| test2   | 
|  3 |        1 |         2 | sql-TEST | ["http:\/\/xx.aa.bb"]					| test3   | 
|  4 |        1 |         2 | sql-TEST | ["http:\/\/nbc.aa.bb"]					| test4   | 
|  5 |        1 |         2 | sql-TEST | ["http:\/\/tt.aa.bb","http:\/\/xx.aa.bb"]		| test5   | 
|  6 |        1 |         2 | sql-TEST | ["http:\/\/xx.aa.bb"]					| test6   | 
|  7 |        1 |         2 | sql-TEST | ["http:\/\/www.cnn.com"]				| test7   | 
+----+----------+-----------+----------+--------------------------------------------------------+---------+

Thanks in advance
Boris
 

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Delete lines starting with XX or YY or ZZ or ....

Hi There! My final task for today is to delete lines starting with certain numbers for e.g., my text block is and i want to delete all lines starting with 11 or 17 or 21 I know i can use multiple sed commands like sed '/^11,/d' <filename> sed '/^17,/d' <filename> sed '/^21,/d'... (2 Replies)
Discussion started by: orno
2 Replies

2. Shell Programming and Scripting

How can i delete a keyword starting with x in unix

I am trying to delete key word starting with x in a unix text file. example, I am trying to delete the words like xaa,xabxbb,xbd and so on.... my input file is some thing like this xaaa w 1234 5678 rwsd ravi xw123 xbc3 ohrd want to delete words xaaa,xw123 and xbc3 from the above... (10 Replies)
Discussion started by: rdhanek
10 Replies

3. Shell Programming and Scripting

How to get full sql table data using shell script

i have a Oracle table like col1 col2 ---- ----- a 1 b 2 c 3 when i write a script for it , it gives me all the data in one column. Please give me the solution which gives the exact result like we see in sql editors. for a in `echo " set feedback off; set pagesize 40;... (1 Reply)
Discussion started by: ss135r
1 Replies

4. Shell Programming and Scripting

delete lines starting with a pattern

i have a file sample.txt containing i want to delete lines starting with 123 neglecting spaces and tabs. but not lines containing 123. i.e. i want files sample.txt as help me thanxx (4 Replies)
Discussion started by: yashwantkumar
4 Replies

5. Shell Programming and Scripting

Delete lines starting with these strings

Platform : RHEL 5.8 I have text file called myapplication.log . In this file, I have around 800 lines which start with the followng three strings PWRBRKER-3493 PWRBRKER-7834 SCHEDULER-ERROR How can I delete these lines in one go ? (13 Replies)
Discussion started by: omega3
13 Replies

6. Shell Programming and Scripting

How to find a phrase and pull all lines that follow until the phrase occurs again?

I want to burst a report by using the page number value in the report header. Each section starts with *PAGE NO:* 1 Each section might have several pages, but the next section always starts back at 1. So I want to find the "*PAGE NO:* 1" value and pull all lines that follow until "*PAGE NO:* 1"... (4 Replies)
Discussion started by: Scottie1954
4 Replies

7. Homework & Coursework Questions

Write a shell script for SQL loader to load data into a staging table

Hi, I'm new to Linux. I'm working on a database, and need to load data in a database table (which I already created) using shell script. The table has two columns - Acct_number (not nullable) and date (timestamp). I'm not able to write a shell script for that. Can any one help me? ... (3 Replies)
Discussion started by: saisudeep
3 Replies

8. Shell Programming and Scripting

Shell scripting unable to send the sql query data in table in body of email

I have written a shell script that calls below sql file. It is not sending the query data in table in the body of email. spool table_update.html; SELECT * FROM PROCESS_LOG_STATS where process = 'ActivateSubscription'; spool off; exit; Please use code tags next time for your code and data.... (9 Replies)
Discussion started by: Sharanakumar
9 Replies

9. Solaris

SQL QUERY to Table Output

Hi I am trying to run sql query from solaris in csh script and send the output to email. Below is my sql query select p.spid,se.program seprogram, se.machine, se.username, sq.sql_text,sq.retrows from v$process p inner join v$session se on p.addr = se.paddr inner join ( select... (2 Replies)
Discussion started by: tharmendran
2 Replies

10. Shell Programming and Scripting

Phrase XML with Huge Data

HI Guys, I have Big XML file with Below Format :- Input :- <pokl>MKL=1,FN=1,GBNo=B10C</pokl> <d>192</d> <d>315</d> <d>35</d> <d>0,7,8</d> <pokl>MKL=1,dFN=1,GBNo=B11C</pokl> <d>162</d> <d>315</d> <d>35</d> <d>0,5,6</d> <pokl>MKL=1,dFN=1,GBNo=B12C</pokl> <d>188</d> (4 Replies)
Discussion started by: pareshkp
4 Replies

LEARN ABOUT DEBIAN

gammu-smsd-odbc

GAMMU-SMSD-ODBC(7)						       Gammu							GAMMU-SMSD-ODBC(7)

NAME

       gammu-smsd-odbc - gammu-smsd(1) backend using ODBC abstraction layer to use any supported database as a message storage

DESCRIPTION

       New in version 1.29.92.

       ODBC  backend  stores  all  data  in  any  database  supported by ODBC, which parameters are defined by configuration (see gammu-smsdrc for
       description of configuration options).

       For tables description see gammu-smsd-tables.

       This backend is based on gammu-smsd-sql.

SUPPORTED DRIVERS

       On Microsoft Windows, Gammu uses native ODBC, on other platforms, unixODBC can be used.

LIMITATIONS

       Due to limits of the ODBC interface, you might have to tweak SQL queries to work in used SQL server, see SQL Queries for more details.

       Partially this can be configured using SQL.

CONFIGURATION

       Before running gammu-smsd you need to create necessary tables in the database. You can use examples given  in  database	specific  backends
       parts of this manual to do that.

       You specify data source name (DSN) as Host in gammu-smsdrc. The data source is configured depending on your platform.

       On Microsoft Windows, you can find instructions on Microsoft website: http://support.microsoft.com/kb/305599

       For unixODBC this is documented in the user manual: http://www.unixodbc.org/doc/UserManual/

EXAMPLE

       Example configuration:

       [smsd]
       service = sql
       driver = odbc
       host = dsn_of_your_database
       sql = sql_variant_to_use
       user = username
       password = password

       See also

	      gammu-smsdrc

AUTHOR

       Michal iha <michal@cihar.com>

COPYRIGHT

       2009-2012, Michal iha <michal@cihar.com>

1.31.90 							 February 24, 2012						GAMMU-SMSD-ODBC(7)
All times are GMT -4. The time now is 10:00 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy