Victor Nares
asked on
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!
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!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
OUTPUT
(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)
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)
no pts pls.
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)
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)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
'case expression'
I know T-SQL documentation itself confuses the distinction, but a `case expression` returns a value
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.
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
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...
>>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)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
@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.
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.
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.
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.....
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.....
ASKER
This spawned some great discustion and a couple of solutions for us.
Thank you all again!
Thank you all again!
A pleasure, and a very interesting question to be involved in. Thank you :)
If so, just search for ' - ' and display the text to the left of it.