June 14, 2021 at 5:52 pm
Hi Guys
Hope everyone is well, i have a field called NAME with some data as shown below
Name
RECA-AA-3-CCC-1
RECA-AB-31-abc-1
RECA-C-3-T
I am trying to write a select statement ( or a script) to remove the Characters 'RECA-' from the names ( shown above) so the output result should be as below
Name
AA-3-CCC-1
AB-31-abc-1
C-3-T
can anyone help me with this?
Thank you
June 14, 2021 at 6:18 pm
Look at the REPLACE function, and replace with '' (2 quotes, without space between them)
June 14, 2021 at 6:20 pm
hi
thank you for the reply
Does the replace , replace the data in the original table?
As i dont want that
June 14, 2021 at 6:24 pm
This will replace the values (overwrite them):
use tempdb;
go
CREATE TABLE Vals(Val VARCHAR(20));
GO
INSERT INTO Vals VALUES
('AAAB-AA-43-DDD-2')
,('RECA-AA-3-CCC-1')
,('RECA-AB-31-abc-1')
,('RECA-C-3-T');
UPDATE Vals
SET Val = REPLACE(Val,'RECA-','')
WHERE Val LIKE 'RECA-%';
If you just want to find them, use something like
SELECT * FROM Vals WHERE Val LIKE 'RECA-%'
June 14, 2021 at 6:24 pm
If you are running SELECT it is only for display.
UPDATE will change the data in the table.
https://docs.microsoft.com/en-us/sql/t-sql/functions/replace-transact-sql?view=sql-server-ver15
June 14, 2021 at 7:03 pm
You can also use Substring() to return a part of the string only.
June 14, 2021 at 7:10 pm
Thank you
i dont want to replace or ovewrite them.
I just want to pull the data without 'RECA-' at the start
June 14, 2021 at 7:34 pm
A few choices:
CREATE TABLE Vals(Val VARCHAR(20));
GO
INSERT INTO Vals VALUES
('AAAB-AA-43-DDD-2')
,('RECA-AA-3-CCC-1')
,('RECA-AB-31-abc-1')
,('RECA-C-3-T');
GO
SELECT
REPLACE(Val,'RECA-','') AS Option1
, CASE WHEN SUBSTRING(val, 1, 5) = 'RECA-'
THEN SUBSTRING(val, 6, LEN(val))
ELSE val
END AS Option2
FROM dbo.Vals AS v
you can also use CHARINDEX to find spots in a string to drive substring if there are other patterns you need.
June 14, 2021 at 8:25 pm
SELECT
STUFF(NAME, 1, CASE WHEN LEFT(NAME, 5) = 'RECA-' THEN 5 ELSE 0 END, '') AS NAME
FROM dbo.table_name
A potential problem with REPLACE is that it will affect the entire string, not just the leading chars. For example, this:
RECA-001-abc-qztb-RECA-LL
would become:
001-abc-qztb-LL
While not at all likely to affect this code, if you only want leading chars changed, it's better to specify that explicitly.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 14, 2021 at 8:28 pm
WHERE NAME NOT LIKE 'RECA-%'
June 15, 2021 at 1:16 pm
WHERE NAME NOT LIKE 'RECA-%'
That will eliminate them from results. The request was to remove the characters 'RECA-' but keep the remaining characters.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply