Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Remove the first two characters in a column value if they meet a certain condition Expand / Collapse
Author
Message
Posted Thursday, December 20, 2012 9:41 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 31, 2012 12:45 PM
Points: 7, 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
Post #1399177
Posted Thursday, December 20, 2012 9:55 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, May 30, 2013 1:44 AM
Points: 45, Visits: 852
Please check this...

update <table name> set name = SUBSTRING (name,3,len(<column name>)) where SUBSTRING(<column name>,1,2)='NA'
Post #1399179
Posted Thursday, December 20, 2012 10:32 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, August 22, 2013 9:07 PM
Points: 53, Visits: 153
UPDATE #TEST_TABLE
SET COLS = SUBSTRING(COLS,3,LEN(COLS)-2)
WHERE LEFT(COLS,2) = 'NA'
Post #1399188
Posted Thursday, December 20, 2012 11:17 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 31, 2012 12:45 PM
Points: 7, Visits: 16
worked like a charm....thanks!
Post #1399206
Posted Friday, December 21, 2012 2:18 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 10:56 AM
Points: 4,832, Visits: 11,200
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1399278
Posted Friday, December 21, 2012 2:54 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 9:39 AM
Points: 554, Visits: 3,176
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?
Post #1399294
Posted Friday, December 21, 2012 3:54 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 7:49 AM
Points: 2,405, Visits: 7,316
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.



Not a DBA, just 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


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1399310
Posted Friday, December 21, 2012 4:50 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 9:39 AM
Points: 554, Visits: 3,176
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
Post #1399330
Posted Friday, December 21, 2012 5:01 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:52 AM
Points: 5,078, Visits: 8,923
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
Post #1399337
Posted Friday, December 21, 2012 5:21 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 1:08 AM
Points: 764, Visits: 587

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
Post #1399349
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse