SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


substring comparison for last 2 characters


substring comparison for last 2 characters

Author
Message
timscronin
timscronin
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1590 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



Cadavre
Cadavre
SSCrazy Eights
SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)

Group: General Forum Members
Points: 9160 Visits: 8492
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" ??


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
timscronin
timscronin
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1590 Visits: 750
wont happen, the records with the 2 characters at the end are state abbreviations added on



Luis Cazares
Luis Cazares
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42378 Visits: 19838
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.
General Disclaimer:
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?


How to post data/code on a forum to get the best help: Option 1 / Option 2
Adi Cohn
Adi Cohn
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: 8287 Visits: 6594
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/
timscronin
timscronin
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1590 Visits: 750
problem is with data like this (my bad should have included it)

1233IL
22M33

Would exclude 2nd record



Steven Willis
Steven Willis
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2069 Visits: 1721
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




 
ScottPletcher
ScottPletcher
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19841 Visits: 7413


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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial:
If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)

Group: General Forum Members
Points: 217284 Visits: 41991
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
sqldriver
sqldriver
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2216 Visits: 2536
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


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