Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Remove the first two characters in a column value if they meet a certain condition


Remove the first two characters in a column value if they meet a certain condition

Author
Message
Brad-282341
Brad-282341
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 16
I have a column of data in a table that I need to update and remove the first 2 characters if they are 'NA'

for example:

NA11345 needs to be 11345
NA458267 needs to be 458267

11587 needs to stay 11587


So basically anything with a NA in the front of the string, (NA) needs to be removed.

What script can be developed to do this?


Thanks
k.thanigaivel
k.thanigaivel
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 852
Please check this...

update <table name> set name = SUBSTRING (name,3,len(<column name>Wink) where SUBSTRING(<column name>,1,2)='NA'
eklavu
eklavu
Valued Member
Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)

Group: General Forum Members
Points: 62 Visits: 173
UPDATE #TEST_TABLE
SET COLS = SUBSTRING(COLS,3,LEN(COLS)-2)
WHERE LEFT(COLS,2) = 'NA'
Brad-282341
Brad-282341
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 16
worked like a charm....thanks!
Phil Parkin
Phil Parkin
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8328 Visits: 19474
eklavu (12/20/2012)
UPDATE #TEST_TABLE
SET COLS = SUBSTRING(COLS,3,LEN(COLS)-2)
WHERE LEFT(COLS,2) = 'NA'


Possibly not relevant here, but if the 'COLS' column is indexed, a better option would be

where cols like 'NA%'



As this will make use of the index, whereas the LEFT function is non-SARGable and will not.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
davidandrews13
davidandrews13
SSC Eights!
SSC Eights! (824 reputation)SSC Eights! (824 reputation)SSC Eights! (824 reputation)SSC Eights! (824 reputation)SSC Eights! (824 reputation)SSC Eights! (824 reputation)SSC Eights! (824 reputation)SSC Eights! (824 reputation)

Group: General Forum Members
Points: 824 Visits: 4450
talking of being SARGable, i was reading this article:
http://en.wikipedia.org/wiki/Sargable

and didn't quite understand this line

The typical thing that will make a sql query non-sargable is to include a function in left part of a condition of a Where clause.


one of the examples given was:


Non-Sargable: Select ... WHERE DateDiff(mm,Date,GetDate()) >= 20



does that mean that if it was written like so:


Select ... WHERE 20 < DateDiff(mm,Date,GetDate())



that it would be Sargable, as the function is now on the Right hand side of a condition in a where clause?
Cadavre
Cadavre
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2594 Visits: 8437
davidandrews13 (12/21/2012)
talking of being SARGable, i was reading this article:
http://en.wikipedia.org/wiki/Sargable

and didn't quite understand this line

The typical thing that will make a sql query non-sargable is to include a function in left part of a condition of a Where clause.


one of the examples given was:


Non-Sargable: Select ... WHERE DateDiff(mm,Date,GetDate()) >= 20



does that mean that if it was written like so:


Select ... WHERE 20 < DateDiff(mm,Date,GetDate())



that it would be Sargable, as the function is now on the Right hand side of a condition in a where clause?


No. A general rule (although not always true) is that if you wrap the column you are searching in a function then it is not SARGABLE, instead you need to wrap your search argument in the function.

Try this example: -
--Standard TestEnvironment of 1,000,000 rows of random-ish data
IF object_id('tempdb..#testEnvironment') IS NOT NULL
BEGIN
DROP TABLE #testEnvironment;
END;

--1,000,000 Random rows of data
SELECT TOP 1000000 IDENTITY(INT,1,1) AS ID,
RAND(CHECKSUM(NEWID())) * 30000 /*(Number of days in range)*/ + CAST('1945' AS DATETIME) /*(Start date, e.g. '1945-01-01 00:00:00*/ AS randomDateTime,
DATEADD(DAY,((ABS(CHECKSUM(NEWID())) % 30000 /*(Number of days in range)*/) + 1),CAST('1945' AS DATE) /*(Start date, e.g. '1945-01-01*/) AS randomDate,
ABS(CHECKSUM(NEWID())) AS randomBigInt,
(ABS(CHECKSUM(NEWID())) % 100) + 1 AS randomSmallInt,
RAND(CHECKSUM(NEWID())) * 100 AS randomSmallDec,
RAND(CHECKSUM(NEWID())) AS randomTinyDec,
RAND(CHECKSUM(NEWID())) * 100000 AS randomBigDec,
CONVERT(VARCHAR(6),CONVERT(MONEY,RAND(CHECKSUM(NEWID())) * 100),0) AS randomMoney
INTO #testEnvironment
FROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2, master.dbo.syscolumns sc3;

CREATE CLUSTERED INDEX cl_testEnvironment_ID ON #testEnvironment(ID ASC);
CREATE NONCLUSTERED INDEX nc_testEnvironment_randomDate ON #testEnvironment(randomDate ASC);



So, we have 1,000,000 rows of sample data.

Let's take a look at your queries: -
DECLARE @TODAY DATE = GETDATE();

-- NON-SARGABLE
SELECT COUNT(*)
FROM #testEnvironment
WHERE DateDiff(mm,randomDate,@TODAY) >= 20;

-- NON-SARGABLE
SELECT COUNT(*)
FROM #testEnvironment
WHERE 20 <= DateDiff(mm,randomDate,@TODAY);



Both perform an index scan on the "nc_testEnvironment_randomDate" index.

I would re-write that as something like this: -
-- SARGABLE
SELECT COUNT(*)
FROM #testEnvironment
WHERE randomDate < DATEADD(mm,-20,DATEADD(MONTH, DATEDIFF(MONTH, 0, @TODAY)+1, 0));



Which is now able to perform an index seek on the "nc_testEnvironment_randomDate" index.


Forever trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


Craig Wilkinson - Software Engineer
LinkedIn
davidandrews13
davidandrews13
SSC Eights!
SSC Eights! (824 reputation)SSC Eights! (824 reputation)SSC Eights! (824 reputation)SSC Eights! (824 reputation)SSC Eights! (824 reputation)SSC Eights! (824 reputation)SSC Eights! (824 reputation)SSC Eights! (824 reputation)

Group: General Forum Members
Points: 824 Visits: 4450
A general rule (although not always true) is that if you wrap the column you are searching in a function then it is not SARGABLE, instead you need to wrap your search argument in the function.


i see. that makes so much sense, i actually feel like i understand it now! cheers Cool
John Mitchell-245523
John Mitchell-245523
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7443 Visits: 15126
Phil Parkin (12/21/2012)
eklavu (12/20/2012)
UPDATE #TEST_TABLE
SET COLS = SUBSTRING(COLS,3,LEN(COLS)-2)
WHERE LEFT(COLS,2) = 'NA'


Possibly not relevant here, but if the 'COLS' column is indexed, a better option would be

where cols like 'NA%'



As this will make use of the index, whereas the LEFT function is non-SARGable and will not.

I was so surprised by this that I decided to do a test.
CREATE TABLE #john (JID int IDENTITY(1,1), testcol char(36));
-- Insert over a million random rows
WITH A1(N) AS (SELECT 0 UNION SELECT 1)
, A2 AS (SELECT a.N FROM A1 a CROSS JOIN A1 b)
, A3 AS (SELECT a.N FROM A2 a CROSS JOIN A2 b)
, A4 AS (SELECT a.N FROM A3 a CROSS JOIN A3 b)
, A5 AS (SELECT a.N FROM A4 a CROSS JOIN A4 b)
, A6 AS (SELECT a.N FROM A5 a CROSS JOIN A3 b)
INSERT INTO #John
SELECT CAST(NEWID() AS char(36))
FROM A6

-- Create indexes
ALTER TABLE #john ADD CONSTRAINT PK_john_JID
PRIMARY KEY CLUSTERED (JID)
CREATE NONCLUSTERED INDEX IX_john_testcol
ON #john(testcol)

-- Sargable
SELECT testcol FROM #john WHERE testcol LIKE 'AB%'
-- Not sargable
SELECT testcol FROM #john WHERE LEFT(testcol,2) = 'AB'


And it's true - the first SELECT used an index seek, the second an index scan. I'd have thought that the query optimizer would have been smart enough to realise that an index could still be used for LEFT.

John
Mitesh Oswal
Mitesh Oswal
Right there with Babe
Right there with Babe (798 reputation)Right there with Babe (798 reputation)Right there with Babe (798 reputation)Right there with Babe (798 reputation)Right there with Babe (798 reputation)Right there with Babe (798 reputation)Right there with Babe (798 reputation)Right there with Babe (798 reputation)

Group: General Forum Members
Points: 798 Visits: 653


DECLARE @tbl TABLE
(
ID INT IDENTITY(1,1),
SomeData VARCHAR(100)
)

INSERT INTO @tbl SELECT 'NA11345'
INSERT INTO @tbl SELECT 'NA113456'
INSERT INTO @tbl SELECT 'RA11345'

UPDATE @tbl SET SomeData = SUBSTRING(SomeData,CHARINDEX('NA',SomeData)+2,LEN(SomeData))
WHERE CHARINDEX('NA',SomeData) = 1
select * from @tbl




Regards,
Mitesh OSwal
+918698619998
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search