Not selecting numbers in first 3 characters of cell only letters

Question

Post reply

Not selecting numbers in first 3 characters of cell only letters

bn_3

Valued Member

Points: 62
More actions
February 23, 2021 at 7:36 am

#3848886

File - left([File],3)
output wanted in case statement...
NJU000123 - NJU
99392288 - NULL

left([File],3 = office except if there are numbers.
How can I improve this left statement to allow only for letters to be pulled while any cells with numbers in the first 3 get left out?

March 5, 2021 at 6:46 am

This was removed by the editor as SPAM

Viewing 5 posts - 1 through 4 (of 4 total)

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

DesNorton SSC-Insane Points: 24386 More actions · Answer 1

It's not 100% clear what you are trying to achieve.

Maybe this will get you on the right path

DECLARE @TestData table (StringVal varchar(10));

INSERT INTO @TestData ( StringVal )
VALUES ( 'NJU000123' ), ( '99392288' );

SELECT d.StringVal
  , preFix = CASE WHEN PATINDEX('%[0-9]%', LEFT(d.StringVal, 3)) = 0 THEN LEFT(d.StringVal, 3) ELSE NULL END
FROM @TestData AS d;

How to post data/code on a forum to get the best help.

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 2

I agree that you aren't specific about what to do when the first three characters are something like N2X. What if you have the string 'N2XY45689'? Do you expect to get 'NX' (Letters in first three characters) or 'NXY' (First three letters). In any case this will also give you a start. Crude but effective. Somebody could probably create a more elegant solution.

declare @string varchar(20) = 'NBD334JU001234'

;With Fix1 (string) as (select case when substring(@string,1,1) not like '[A-Z]' 
  then stuff(LEFT(@string,3),1,1,' ') 
  else LEFT(@string,3) end)
,Fix2 (string) as (select case when substring(string,2,1) not like '[A-Z]' 
  then stuff(string,2,1,' ') 
  else string end
from Fix1)

,Fix3 (string) as (select case when len(string) = 3 and substring(string,3,1) not like '[A-Z]' 
  then stuff(string,3,1,' ') 
  else string end
from Fix2)

SELECT NULLIF(replace(STRING,' ',''),'')
FROM Fix3

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Phil Parkin SSC Guru Points: 246960 More actions · Answer 3

MaryWard, this is a SQL Server forum. Moving data to Excel and processing it there is a really bad idea!

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin