February 23, 2021 at 7:36 am
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?
February 23, 2021 at 7:45 am
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;
February 23, 2021 at 12:00 pm
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
March 5, 2021 at 6:46 am
This was removed by the editor as SPAM
March 5, 2021 at 10:28 am
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply