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 123»»»

substring comparison for last 2 characters Expand / Collapse
Author
Message
Posted Monday, July 1, 2013 8:58 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: Tuesday, February 18, 2014 12:52 PM
Points: 502, Visits: 750
I have a field with data like below I need to join on

patid
123453
124344ME
323390
3233MS

I need to remove the last 2 charcters if they are not numeric. So my results for the join should be

patid
123453
124344
323390
3233







Post #1469102
Posted Monday, July 1, 2013 9:05 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:36 AM
Points: 2,379, Visits: 7,584
timscronin (7/1/2013)
I have a field with data like below I need to join on

patid
123453
124344ME
323390
3233MS

I need to remove the last 2 charcters if they are not numeric. So my results for the join should be

patid
123453
124344
323390
3233






What would you do with "1234C5" and "12345C" ??



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 #1469106
Posted Monday, July 1, 2013 9:11 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: Tuesday, February 18, 2014 12:52 PM
Points: 502, Visits: 750
wont happen, the records with the 2 characters at the end are state abbreviations added on


Post #1469107
Posted Monday, July 1, 2013 9:13 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 10:05 AM
Points: 3,774, Visits: 8,465
A possible solution would be like this. I'm worried about performance though. Couldn't you correct the data in the first place?

CREATE TABLE #Table1( patid varchar(10))
CREATE TABLE #Table2( patid varchar(10))

INSERT INTO #Table1 VALUES('123453'),('124344ME'),('323390'),('3233MS')
INSERT INTO #Table2 VALUES('123453'),('124344'),('323390'),('3233')

SELECT *
FROM #Table1 a
JOIN #Table2 b ON CASE WHEN a.patid NOT LIKE '%[^0-9]%' THEN a.patid ELSE LEFT(a.patid, LEN(a.patid) - 2) END = b.patid


DROP TABLE #Table1
DROP TABLE #Table2




Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1469108
Posted Monday, July 1, 2013 9:15 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 5:28 AM
Points: 2,114, Visits: 5,502
Here is one more way:

create table Demo (Col1 varchar(10))
go

insert into Demo(Col1) values ('123453'),('124344ME'),('123453'),('124344ME'),('323390'),('3233MS')

select Col1, substring(Col1,1, CASE WHEN Col1 like '%[aA-zZ]' THEN len(Col1)-2 else len(Col1) end)
from Demo



--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #1469109
Posted Monday, July 1, 2013 9:16 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: Tuesday, February 18, 2014 12:52 PM
Points: 502, Visits: 750
problem is with data like this (my bad should have included it)

1233IL
22M33

Would exclude 2nd record



Post #1469111
Posted Monday, July 1, 2013 11:05 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
Create some sample data:

IF OBJECT_ID('tempdb..#SampleData') IS NOT NULL
DROP TABLE #SampleData

CREATE TABLE #SampleData (
[ID] INT IDENTITY(1,1) NOT NULL,
[strVal] NVARCHAR(50) NULL,
PRIMARY KEY (ID))

INSERT INTO #SampleData
VALUES
('123453ME'),('124344'),('323390'),('32339826455MS'),
('345678'),('39WA'),('986545'),('1234AK'),
('345XX8'),('398644GA'),('F986545'),('98234AZ')


Now query the data and strip off the unwanted suffixes
as well as eliminating any entries with an embedded alpha char.

SELECT
r.strVal
FROM
(
SELECT
(CASE
WHEN PATINDEX('%[A-Za-z]%',RIGHT(strVal,2)) > 0
THEN REPLACE(strVal,RIGHT(strVal,2),'')
ELSE strVal
END) AS strVal
FROM
#SampleData AS sd
) r
WHERE
PATINDEX('%[A-Za-z]%',strVal) = 0


 
Post #1469163
Posted Monday, July 1, 2013 2:25 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 10:25 AM
Points: 2,198, Visits: 3,310

SELECT
mt.##col1##, ..., jt.##co11##, ...
FROM dbo.maintable mt
LEFT OUTER JOIN jointable jt ON
--ignore last two bytes of patid if they are alpha, because it's a state abbrev, not part of key value
jt.key = LEFT(mt.patid, LEN(mt.patid) - CASE WHEN RIGHT(mt.patid, 2) LIKE '[a-z][a-z]' THEN 2 ELSE 0 END)



Edit: Added sql code tags to colorize code.


SQL DBA,SQL Server MVP('07, '08, '09)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1469218
Posted Monday, July 1, 2013 5:08 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 1:53 PM
Points: 35,366, Visits: 31,905
timscronin (7/1/2013)
wont happen, the records with the 2 characters at the end are state abbreviations added on


Then it's easy... Build a "State" table (you should have one anyway) and the your WHERE clause would look like the following...


WHERE RIGHT(PatID,2) IN (SELECT StateAbbv FROM dbo.State)


Be advised that no matter what you do, performance is going to stink because of the PatID column being wrapped in a function. What I'd recommend doing if you need to do this a lot is to make the RIGHT(Pat,2) a PERSISTED calculated column in the table and index it.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1469239
Posted Tuesday, July 2, 2013 9:22 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 11:13 AM
Points: 467, Visits: 1,883
I use the following SVF to extract integers only from phone number columns. So, you would run the following and it would return only the numbers from your patid column.

select dbo.ExtractInteger(patid) as  [patid]
from table


USE [Sample]
GO
/****** Object: UserDefinedFunction [dbo].[ExtractInteger] Script Date: 07/02/2013 11:17:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[ExtractInteger](@String VARCHAR(2000))
RETURNS VARCHAR(1000)
AS
BEGIN
DECLARE @Count INT
DECLARE @IntNumbers VARCHAR(1000)
SET @Count = 0
SET @IntNumbers = ''

WHILE @Count <= LEN(@String)
BEGIN
IF SUBSTRING(@String,@Count,1) >= '0'
AND SUBSTRING(@String,@Count,1) <= '9'
BEGIN
SET @IntNumbers = @IntNumbers + SUBSTRING(@String,@Count,1)
END
SET @Count = @Count + 1
END

RETURN @IntNumbers
END

Post #1469605
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse