December 20, 2012 at 9:41 pm
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
December 20, 2012 at 9:55 pm
Please check this...
update <table name> set name = SUBSTRING (name,3,len(<column name>)) where SUBSTRING(<column name>,1,2)='NA'
December 20, 2012 at 10:32 pm
UPDATE #TEST_TABLE
SET COLS = SUBSTRING(COLS,3,LEN(COLS)-2)
WHERE LEFT(COLS,2) = 'NA'
December 20, 2012 at 11:17 pm
worked like a charm....thanks!
December 21, 2012 at 2:18 am
eklavu (12/20/2012)
UPDATE #TEST_TABLESET 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.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
December 21, 2012 at 2:54 am
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?
December 21, 2012 at 3:54 am
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.
December 21, 2012 at 4:50 am
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 😎
December 21, 2012 at 5:01 am
Phil Parkin (12/21/2012)
eklavu (12/20/2012)
UPDATE #TEST_TABLESET 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
December 21, 2012 at 5:21 am
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
December 21, 2012 at 5:56 am
Mitesh Oswal (12/21/2012)
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
I'm a bit surprised that you posted this after the preceding discussion on index usage. Also, why bother with all that SUBSTRING nonsense when you already know that all you want to do is replace the first two characters?
Instead of
SomeData = SUBSTRING(SomeData,CHARINDEX('NA',SomeData)+2,LEN(SomeData))
Use
SomeData = STUFF(SomeData,1,2,'')
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
December 27, 2012 at 7:48 pm
Nice. Now I'm REALLY learning SQL.
Sometimes reading is not enough to learn.
You need to interact or post something so that someone can give comments and give a better option.
Thanks a lot!
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply