Return Leading Digits from a Varchar String

Thank you all in advance!

I have a column with data that looks like this

1 - Snoopy
2 - Marmadook
3 - Fido
.
.
.
.
14 - Kujo


When I query the column/table, I would like to display only the leading digits. digit values do not go over 99.

Thanks again!
Victor Naresdb DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kent OlsenDBACommented:
Are there always leading digits and are they always followed by ' - '?

If so, just search for ' - ' and display the text to the left of it.
0
PortletPaulEE Topic AdvisorCommented:
Assuming there is at least one space after the number, then search for that first space, and take just the left portion of the column up to that position:

LEFT( your_column, CHARINDEX ( ' ', your_column, 1 )   )
0
Scott PletcherSenior DBACommented:
LEFT(your_column, PATINDEX('%[^0-9]%', your_column) - 1)

That removes any dependency on specific naming pattern, other than that the digits come first.  For example:

SELECT *, LEFT(your_column, PATINDEX('%[^0-9]%', your_column) - 1) AS leading_digits
FROM (VALUES('1 - Snoopy'),('2 - Marmadook'),('3 - Fido'),('14 - Kujo'),
('5Lassie'),('6    Rin Tin Tin'),('7(Spiderman)')) AS data(your_column)
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

Mark WillsTopic AdvisorCommented:
Concur with Scott,

While he was answering, I was on phone and only just saw his answer which is same as mine (in essence) below. Though, didnt have the -1 in determining the length (and is needed)

declare @str varchar(200) = '12. abdcdef'

select left(@str, patindex('%[^0-9]%',@str))

-- adding in the -1

select left(@str, patindex('%[^0-9]%',@str) - 1)

Just swap @str for your data column and add in the from datasource....  Might want to add in some checks (or a case condition) to make sure the patindex retruns a non-zero position...
0
Pawan KumarDatabase ExpertCommented:
Minor Addition..
(no pts pls)The above will fail if have data like...just 33, to handle that you need to use a case statement like below

SELECT *,CASE WHEN PATINDEX('%[^0-9]%', your_column) = 0 THEN your_column ELSE LEFT(your_column, PATINDEX('%[^0-9]%', your_column) - 1) END 
AS leading_digits FROM (VALUES('1 - Snoopy'),('2 - Marmadook'),('3 - Fido'),('33'),('14 - Kujo'),
('5Lassie'),('6    Rin Tin Tin'),('7(Spiderman)')) AS data(your_column)

Open in new window


OUTPUT
/*------------------------
SELECT *,CASE WHEN PATINDEX('%[^0-9]%', your_column) = 0 THEN your_column ELSE LEFT(your_column, PATINDEX('%[^0-9]%', your_column) - 1) END 
AS leading_digits FROM (VALUES('1 - Snoopy'),('2 - Marmadook'),('3 - Fido'),('33'),('14 - Kujo'),
('5Lassie'),('6    Rin Tin Tin'),('7(Spiderman)')) AS data(your_column)
------------------------*/
your_column      leading_digits
---------------- ----------------
1 - Snoopy       1
2 - Marmadook    2
3 - Fido         3
33               33
14 - Kujo        14
5Lassie          5
6    Rin Tin Tin 6
7(Spiderman)     7

(8 row(s) affected)

Open in new window

no pts pls.
0
Scott PletcherSenior DBACommented:
Good point.  I needed to foolproof the non-numeric check:

LEFT(your_column, PATINDEX('%[^0-9]%', your_column + '.') - 1)

SELECT *,
    LEFT(your_column, PATINDEX('%[^0-9]%', your_column + '.') - 1) AS [leading_digit(s)]
FROM (VALUES('1 - Snoopy'),('2 - Marmadook'),('3 - Fido'),('14 - Kujo'),
('5Lassie'),('6    Rin Tin Tin'),('7(Spiderman)'),('8'),('33'),('777')) AS data(your_column)
0
Mark WillsTopic AdvisorCommented:
Yeah, doing the  +'.'  to a string is fine except if NULL, or if only numbers (like your 777 example)....

So long as the string has length, we can always subtract 1. If no numbers then it will return a 1. Adding a '.' might yield an incorrect result.

Was thinking:

select case when patindex('%[^0-9]%',@str) > 1 then left(@str, patindex('%[^0-9]%',@str) - 1) else 'format issue' end

but I am sure the asker is getting the idea now, and your original extracting the number component  works


EDIT: just noticed pawan's comment using the suggestion of a case statement - sorry to all, just started back posting in open questions and not remembering to refresh first... Duh... Not for the slow typist....
0
PortletPaulEE Topic AdvisorCommented:
'case expression'

Expressions, which can produce either scalar values, or tables consisting of columns and rows of data
Statements, which may have a persistent effect on schemata and data, or may control transactions, program flow, connections, sessions, or diagnostics.

I know T-SQL documentation itself confuses the distinction, but a `case expression` returns a value
1
Pawan KumarDatabase ExpertCommented:
NO PTS PLS.

>>Yeah, doing the  +'.'  to a string is fine except if NULL, or if only numbers (like your 777 example).... So long as the string has length, we can always subtract 1. If no numbers then it will return a 1. Adding a '.' might yield an incorrect result.

NO, In case of NULL we will get NULL as OUTPUT with the SCOTT's code ...which is fine.  We do not need CASE Statement there. CASE will unnecessarily increase the code here. Even if you add case then also it will give you the same result . So I would say its a developer's choice how he wants to write the code. :)

See below tested sample...

/*------------------------
SELECT * 
,LEFT(your_column, PATINDEX('%[^0-9]%', your_column + '.') - 1) AS [leading_digit(s)]
FROM 
(VALUES('P1'),(NULL),('1 - Snoopy'),('2 - Marmadook'),('3 - Fido'),('14 - Kujo'),
('5Lassie'),('6    Rin Tin Tin'),('7(Spiderman)'),('#'),('8'),('33'),('777')) AS data(your_column)
------------------------*/
your_column      leading_digit(s)
---------------- ----------------
P1               
NULL             NULL
1 - Snoopy       1
2 - Marmadook    2
3 - Fido         3
14 - Kujo        14
5Lassie          5
6    Rin Tin Tin 6
7(Spiderman)     7
#                
8                8
33               33
777              777

(13 row(s) affected)

Open in new window

0
Mark WillsTopic AdvisorCommented:
@pawan,

The asker did say that 'digit values do not go over 99'

So the 777 example is an invalid format.

If worried about code overheads, and assuming the format is always compliant / correct as posted, then all we really need is rtim(left(@str,2))

But we should take the more robust and responsible step of adding in some checks and tests to ensure the results comply with the requirement. Data being what it is, there is always the possibility of unknowns. Now, while we cannot foresee all unknowns, we can generically handle strings that might not comply with the format.  

In that regard, I deliberately generate a 'format issue' error - which could really be anything according to what the asker would prefer.

After all, I did also say "but I am sure the asker is getting the idea now, and your original extracting the number component  works"

And in my case expression, the test should really be that the patindex returns a result between 1 and 3 to deliver a valid format as suggested, any other results means the string doesnt comply with the format. So, I would probably error anything that doesnt fit into 'nn - xxxx' type format. Which includes 777 and NULL.

Scott Pletcher undersood what I was getting at, and subsequently posted the + '.'

Is this how we answer and interact these days ? Again, it has been a while since posting in open questions....
0
Scott PletcherSenior DBACommented:
@Mark:

Please look more closely at my code.  I only add the '.' to the value being scanned for non-numerics, not to the string used to return a value.  Thus, the added '.' will never be included in the data returned by the code.

The asker did say that 'digit values do not go over 99'
So the 777 example is an invalid format.

For now.  Who knows next week?  If there are never any values over two digits, my code won't hurt anything anyway.  If an entry does come thru with three digits, say '123 - King Kong', I wouldn't want to return "12" as that's effectively a false value.  Sometimes errors do occur in data entry/generation, and it's best to catch those as early as possible.
1
Mark WillsTopic AdvisorCommented:
Scott, yes I did read your code and understand the use of the fullstop. At no stage did I ever imply that adding the '.' was erroneous.

I did say that it might yield incorrect results. '777' doesnt have any alpha characters (nor does NULL). so by suffixing with an alpha character, we are artificially forcing a result.

Maybe that is fine and the asker would like to use that - again I repeat that 'I am sure the asker is getting the idea now ... your code works' I actually preferred your original patindex example because it wouldnt have returned just 777. So made the mistake of wanting to clarify what I meant by 'Might want to add in some checks ...'

Yes, '123 - king kong' maybe legitimate at some stage, but for now we are told digit values do not go over 99. For the very same reason why you then say '12' is a false value, I too was suggesting that it is possible to get false results given the constraints / requirement spelled out by the asker for the current requirement...

Pawan, please dont bother with shouting 'no points please' if all you want to do is prove me wrong. I dont buy it at all. A polite correction is all that is needed. If you recall, my post didnt correct the length (it included the trailing space) and I pointed that out when concurring with Scott's post to reinforce his answer.

I am flabbergasted that this is how experts participate these days. If this is the new norm, then it has indeed changed.....
0
Victor Naresdb DeveloperAuthor Commented:
This spawned some great discustion and a couple of solutions for us.

Thank you all again!
0
Mark WillsTopic AdvisorCommented:
A pleasure, and a very interesting question to be involved in. Thank you :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.