A Sql Query to search and replace specific prefixed strings?

Alright I have something like this(I messed it up...) -

Id Name City 1 XXX - New Plano 2 XXX - XXX - New1 Dallas 3 XXX - XXX - XXX - New2 Sacramento 4 XXX - New3 Houston 5 XXX - XXX - New4 Austin

So, I want to replace all the occurrence with more that one XXX prefix to just 1. For e.g id 2 should have Name=XXX - New2. How would go about achieving this ? Even a query basically to check for a prefix and replace it with the name would work I think and then I can add the prefix again ? I mean set all the records just to say New,New2..so on...and then I can add XXX prefix to it ?

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

You can remove all instances of 'XXX - ' with the string replace function.

UPDATE tableName
SET Name = Replace(Name, 'XXX - ', '')

Alternately, to keep a single instance you could use:

UPDATE tableName
SET Name = 'XXX - ' + Replace(Name, 'XXX - ', '')
WHERE CHARINDEX('XXX - ', Name) > 0

You want to strip out occurrences 2 to n of the string "XXX - ". This assume the data at the end needs preserved:

SELECT 'Starting' --also sets @@ROWCOUNT
WHILE @@ROWCOUNT <> 0
UPDATE
Bollixedtable
SET
name = STUFF (name, 7, 6, '')
WHERE
SUBSTRING (name, 7, 6) = 'XXX - '

Otherwise, this removes the XXX and resets your data, based on the XXX - New(id-1) pattern with no filter if you want to start again

UPDATE
Bollixedtable
SET
name = 'XXX - New' + CAST(id-1 AS varchar)

Edit: updated for clarity

Depends a lot on the data and how uniform it is. If all your data falls into the formats that you have in your question, then the simplest would be:

SELECT
ID,
'XXX - ' + SUBSTRING(NAME, CHARINDEX('NEW',NAME),LEN(Name)-CHARINDEX('NEW',NAME)) AS Name,
CITY
FROM
FOO

Otherwise, it could get a lot more complicated. Tell us more about the uniformity of your data and what the format's you will run into are, i.e. could you have XXXX - XXXX - XXXX - XXXX - ad infintum?

Update Table
Set Name = Replace(
Replace(
Replace( Name, ' - XXX - ', ' - ')
, ' - XXX - ', ' - ')
, ' - XXX - ', ' - ')

This solution should handle most variants. For example, it will handle all the way out to:

XXX - XXX - XXX - XXX - XXX - XXX - XXX - XXX - New2.

If you need to handle it out further, you can simply add another Replace call.

Assuming that you just wanted to strip the string of all the parts before the last "-" and then add a prefix again, you can use this:

UPDATE tblWhatever
SET Name = 'NewPrefix - ' + RIGHT(Name, CHARINDEX(' - ',REVERSE(Name))-1)

This just basically, searches for the last occurrence of "-" and then uses the right() function to get the last part of the string. It then appends the prefix onto that.

This is essentially what you described in the last part of your question.

update myTable
set name = rtrim(ltrim(replace(name, 'XXX - XXX', 'XXX - ')))

update myTable
set name = rtrim(ltrim(substring(name, charindex(name, '-'), len(name))))
where name like '%-%'

declare @loop bit
set @loop = 1

while @loop
begin
update table
set name = right(name,len(name)-6)
where left(name,12) = 'xxx - xxx - '

set @loop = @@rowcount
end

Category:sql Views:0 Time:2011-01-28

Related post

  • SQL query for a carriage return in a string and ultimately removing carriage return 2009-08-26

    SQL query for a carriage return in a string and ultimately removing carriage return I have some data in a table and there are some carriage returns in places where I don't want them. I am trying to write a query to get all of the strings that contain

  • How do you create a sql query of dynamic fields using a final string in VoltDB? 2011-12-08

    I want to create a sql query of dynamic fields that are decided at runtime, such as: SELECT some, random, field FROM table WHERE id = ? Because there is a restriction that you must declare an instance variable SQLStmt: public final SQLStmt sql = new

  • SQL Query to search schema of all tables 2009-08-12

    I am working on a SQL Server 2008 Db that has many tables in it (around 200). Many of these tables contain a field by the name "CreatedDate". I am trying to identify all the table schema with this particular field. Is there a SQL query to do this? Ap

  • SQL query for search criteria 2012-04-02

    In my table there are codes which are basically decimal values. For example the code column contains data like 001.0, 00.10, 002.0, 00.20 etc. I have to write a SQL query which shows following result while searching the code values from UI: If the us

  • Using Like T SQL Statement to search and replace quotation marks 2010-11-05

    Because the quotation mark is a special character is there a way to replace all quotation with nothing Original Query Update table X Set mycolumn = Replace(mycolumn,''','') Where mycolumn like '%'%' Error (Incorrect SQL Syntax - Unclosed quotation ma

  • php/mysql: sql query for searching in all fields 2011-04-11

    i'm having a problem with malformed html syntax in my database (eg. &amp; instead of &) - is there a way to run a sql query which will search in all textfields? thanks --------------Solutions------------- There's no query syntax more sophisti

  • SQL: Query to Search All possible words 2011-10-13

    I have a form with an input box where the user can specify their names, the names may be two or more words, eg John Smith or John Michael Smith. I have to write a query that returns records containing all words in the name presented. So query will re

  • SQL query to search multiple values from same column 2014-03-26

    My table has field like empID and Skills, where one empID can have several Skills entered in different rows. empID skill G001 ASP.NET G002 VB G003 SQL Server G001 C# G002 Crystal report Now I want to search on multiple skills, like which id have skil

  • SQL Query to search partial data in one field 2011-12-29

    I have the following data in mysql: file-id 32-534 32-536 32-537 32-584 32-594 46-865 46-863 46-837 46-867 I want to write SQL query to enlist all the data with either 32- or 46-. How can I do that? Select * from TABLE WHERE file-id = ???? ----------

  • can I pass a list in sql query to search 2012-04-18

    I want a sql query which serves to return a list of values of the inputted and get a returned list. suppoze I have two fields in a table. values [1-a,2-b,3-c,4-d], I pass in [a,c] how do I get [1,3]? --------------Solutions------------- You need to i

  • Tricky SQL query requiring search for contains 2012-09-08

    I have data such as this: Inventors column in my table Hundley; Edward; Ana Isler; Hunsberger Hunsberger;Hundley Names are separated by ;. I want to write a SQL query which sums up the count. Eg. The result should be: Hundley 2 Isler 1 Hunsberger 2 E

  • Search and replace hundreds of strings in tens of thousands of files? 2010-04-21

    I am looking into changing the file name of hundreds of files in a (C/C++) project that I work on. The problem is our software has tens of thousands of files that including (i.e. #include) these hundreds of files that will get changed. This looks lik

  • Search and replace a particular string in a file using Perl 2011-11-22

    Possible Duplicate: How to replace a string in an existing file in Perl? I need to create a subroutine that does a search and replace in file. Here's the contents of myfiletemplate.txt: CATEGORY1=youknow_<PREF> CATEGORY2=your/<PREF>/goes/

  • How can the type of SQL query parameter be controlled when using a string property in .NET Entity Framework 2012-01-22

    On the database table there is a column of type char(2). When I use a parameter in linq such as .Where(x => x.Code == code) the SQL query parameter is translated into varchar(max) where I would really like it to be char(2) in order to avoid an imp

  • Search and replace specific query string parameter value in javascript 2011-03-24

    I have a string which is something like this : a_href= "www.google.com/test_ref=abc"; I need to search for test_ref=abc in thisabove strinng and replace it with new value var updated_test_ref = "xyz"; a_href ="www.google.com/test_ref=updated_test_ref

  • SQL Query wildcard search 2011-03-13

    Im writing a C# web page thats tied to a listview. My client would like to be able to type in something or part of something that it would show results. For example he wants a textbox where he may put in a phone number, part of a phone number, a name

  • Sql query for search program in vb.net 2011-10-21

    Trying to figure out the right way to query a MySql database and find a user defined variable from a Textbox.Text property. I'm not sure what to put for the where clause in order to search every cell of every row for a match. I have looked at coalesc

  • SQL query to search for a record that has a certain text? 2010-02-22

    How can I query for a record that has certain text input from the user? For example in my table adapter function: SELECT Word, Description, Example FROM WordLists WHERE (Word LIKE @SearchKey OR Description LIKE @SearchKey OR Example LIKE @SearchKey)

  • sql query relative searching to previous searched words 2011-01-16

    I have list of word in table. I want to search for all records contain e.g. book and books, pen and pens, that means, for all the word which ends with 's'. The query should show the word without 's' and the word with 's' too. not a query "SELECT * FR

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

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