SQL query to replace a substring

I wish to write a query which will replace the value0.00 with "-" in a column. The value should only be replaced if the value is 0.00 and not if 230.00

I did this query replace(SUBSTRING(cast(columname AS varchar(7)),0,7),'0.00','-') but it replaces 230.00 as 23-

Please help

-------------Problems Reply------------

Assuming a table format like so:

`id` | `cost`
1 | 230.00
2 | 543.65
3 | 0.00

Then a query of

SELECT CASE WHEN cost=0 THEN '-' ELSE cost END FROM table;

will return

230.00
543.65
-

use:

if (SUBSTRING(cast(columname AS varchar(7)),0,7) = '0.00', '-', SUBSTRING(cast(columname AS varchar(7)),0,7))

CASE WHEN columname = 0 THEN '0.00' ELSE replace(SUBSTRING(cast(columname AS varchar(7)),0,7),'0.00','-') END

Category:sql Views:0 Time:2011-07-15

Related post

  • SQL query to replace all occurrences of space in a table with underscore 2009-12-03

    How can I write a SQL query to replace all occurrences of space in a table with underscore without writing individual statements for each column? --------------Solutions------------- UPDATE [table_name] SET [field_name] = replace([field_name],' ','_'

  • sql query to replace a blank cell with any text in access 2007 column 2010-03-12

    I have an access table which has some cells as blank ( no data ) in a particular column. how i write an sql query to replace a blank cell with any text in access 2007 column any help appreciated. i have already tried the sql query update tableA set c

  • A SQL query that replaces null values. 2008-10-15

    I need a SQL query that returns ContactDate, SortName, City, ContactType, and Summary from the tables below. If any value is null, I need it to return the text “No Entry”. ContactTable ContactID ContactDate UserID Summary ContactType SortName UserTab

  • SQL query to replace all cells with new data 2013-05-27

    I have a mysql database, managed by phpmyadmin. I have about 14,000 records - all pin codes for a script that users use to enter a contest. I need to update the DB to reflect this years codes. I have been supplied the codes in an Excel file. There ar

  • T-SQL Query to replace a symbol or character in one xml node value of XML data type column? 2011-08-11

    I want to replace plus symbol with underscore in the one xml node of a particular table. Example: In the below input, i want to replace plus symbol only in filepath node and NOT filename node. Table column data type is XML and NOT varchar. Input:

  • SQL Query to Replace Multiple Matching Rows With a New Row 2011-10-03

    For simplicity, suppose I have a SQL table with one column, containing numeric data. Sample data is 11 13 21 22 23 3 31 32 33 41 42 131 132 133 141 142 143 If the table contains ALL the numbers of the form x1, x2, x3, but NOT x (where x is all of the

  • Write a SQL Query to replace values and include all the Dates 2010-05-19

    Well I have this - Table DimDate- Date Table Employee- Id,Name,Points,Date Now the Employee table has points for everyday unless they did not come...so the Date does not have all the Dates entries... I mean for e.g in a week he did not come for 2 day

  • Is it possible to replace values in SQL query? 2011-08-02

    I would like to know if it is possible in an SQL query to replace some values by something else, or if I need to do that in post-processing. Let me explain. I have this table: |username| accepted | |--------|----------| | toto | NULL | |--------|----

  • Edit XML with SQL query SQL Server 2008 R2 2012-01-19

    I have the following problem and hope someone could help. I have a SQL Server database with a couple thousand rows. Every row consist of a column with an ID and a column with XML data. This XML data looks something like: <record id="1"> <fie

  • SQL Query using '?' in Microsoft Script Editor 2012-12-28

    In Excel 2003 I could run a Report based on a SQL Query and replace the report's hard-coded Start Date with '?' which will prompt the User for a Start Date. Then when the Report is Refreshed it would know to get the Start Date from Cell A2, for examp

  • Single quote in text field used in SQL query 2013-11-21

    Hi, I have text fields in my database that might contain words like St James' Hospital. I have configured a double-click event that opens an editing form when the name is double-clicked, but because of the single quote in the text field, the code is

  • SQL query with SUBSTRING and CONTAINS 2011-09-28

    I have following table ID | Name | Url Each Url contains address to file fe: "http://blah.blah.com/abcde.bin". Address is always same but file name. In need SQL query to update all cells in this column, where Url contains http and store only file nam

  • Replace Multiple Strings in SQL Query 2009-02-02

    I'm writing a SQL query in SQL Server in which I need to replace multiple string values with a single string value. For example Product Quantity ------- -------- Apple 2 Orange 3 Banana 1 Vegetable 7 Dairy 6 would become Product Quantity ------- ----

  • How to replace single-quote with double-quote in sql query - oracle 10g? 2009-10-19

    How can I replace single-quote (') with double-quote (") in sql query - oracle 10g? --------------Solutions------------- This should work: UPDATE myTable SET myField = REPLACE(myField, '''', '"'); You can use Ansi-codes as well, to make it more cryst

  • Use Replace Method In Linq-Sql Query 2010-07-10

    I used RegEx to remove HTML TAGS within LINQ-SQL query but the following error has thrown: Method 'System.String Replace(System.String, System.String, System.String)' is not supported for execution as SQL. Help helpDBSession = new Help(); IEnumerable

  • Replace in SQL Query 2010-11-24

    I have a question regarding some SQL statement. I search for a name, e.x. "Anne Marie Parker". This search query I pass over to my script. The script uses the following sql code: SELECT * FROM table WHERE name LIKE '%(searchquery)%' (searchquery) wil

  • Android: replace values of a cursor/sql query 2011-01-13

    In my SQL database table there are colums _id, int dayofweek, String text ... I now, do a SQL query on that table to get a cursor to the results. The results then are shown in a listView: private void showBlocks() { Cursor blocks = ttDB.getBlocks();

  • T-SQL migration of working Access SQL Query with an issue on writing the CASE replacement for IIF 2011-02-11

    I have two tables BMReports_FPN_Curves and BMReports_BOA_Curves each consisting of a Name, Datetime, Period and Value, for example: BM_UNIT_NAME RunDate Period FPN (or BOA) T_DRAXX-1 2010-12-01 00:03:00 1 497 With the RunDate field incrementing by on

  • Unexpected SQL query result in Android with "replace" 2011-04-22

    I am writing an application for Android that downloads a games leaderboard at frequent intervals. The results are stored in a database. To keep a download seperate from the live results I download into one table then copy over to another. The leaderb

Copyright (C) dskims.com, All Rights Reserved.

processed in 0.255 (s). 11 q(s)