TRIM, using IF/THEN based on character count

Question

TRIM, using IF/THEN based on character count

SSCarpal Tunnel

Points: 4298

July 1, 2019 at 4:03 pm

How can I take the following (temp table) code and TRIM based on the number of characters in these SSNs? Note that the second SSN has an extra character and I want to ignore it and choose the first 4 characters of the last set of characters:

IF OBJECT_ID('dbo.TestSSNTrim','U') IS NOT NULL
  DROP TABLE [dbo].[TestSSNTrim]

CREATE TABLE [dbo].[TestSSNTrim] (OldSSN varchar(12), FirstName varchar(25), LastName varchar(25))

INSERT INTO [dbo].[TestSSNTrim] ([OldSSN], [FirstName], [LastName]) VALUES ('235-55-7777', 'Fred', 'Brown')
INSERT INTO [dbo].[TestSSNTrim] ([OldSSN], [FirstName], [LastName]) VALUES ('236-44-88887', 'Lisa', 'Evans')

SELECT
  t.OldSSN
  ,LeftTrim = LEFT(t.OldSSN,4)
  ,RightTrim = RIGHT(t.OldSSN,4)
  ,[FirstName]
  ,[LastName]
FROM [dbo].[TestSSNTrim] AS t

IF OBJECT_ID('dbo.TestSSNTrim','U') IS NOT NULL
  DROP TABLE [dbo].[TestSSNTrim]
GO

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jonathan AC Roberts SSCoach Points: 18355 More actions · Answer 1

SELECT 
  t.OldSSN
  ,LeftTrim = LEFT(t.OldSSN,4)
  ,RightTrim = RIGHT(t.OldSSN,4)
  ,[FirstName]
  ,[LastName]
  ,LEFT(SUBSTRING(OldSSN,8,100),4)
  ,LEFT(SUBSTRING(OldSSN,CHARINDEX('-', OldSSN,6)+1,100),4)
FROM [dbo].[TestSSNTrim] AS t

DaveBriCam SSCarpal Tunnel Points: 4298 More actions · Answer 2

DaveBriCam

SSCarpal Tunnel

Points: 4298

July 1, 2019 at 4:43 pm

#3658954

Thank you... works perfectly!

Michael L John One Orange Chip Points: 27055 More actions · Answer 3

Are you actually storing SSN's in clear text?

How can you be sure that the left most characters are the correct characters?

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Jonathan AC Roberts SSCoach Points: 18355 More actions · Answer 4

Jonathan AC Roberts

SSCoach

Points: 18355

July 1, 2019 at 4:45 pm

#3658956

Or an even shorter version:

SUBSTRING(OldSSN,8,4)

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 5

Here is another way: right(cast(@ssn As char(11)), 4)

For SSN - you should either set the column length to CHAR(11) or CHAR(9) and enforce the format...with or without dashes. That can be done simply with a check constraint and you avoid having to do anything like the above.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

ScottPletcher SSC Guru Points: 101098 More actions · Answer 6

It's a waste of space to store dashes: simply char(9) will do. Assuming this is some type of temporary table, otherwise you need to encrypt the data which means you'd need a varbinary column.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".