Hello SSC,
So, I inherited this code and trying to understand what this SUBSTRING is doing. I can see that the substring starts on the first instance on non-numeric data, and if I am not mistaken, the length is the entire column concatenated with a '.'?
Any help would be greatly appreciated!
SELECT SUBSTRING(COL, PATINDEX('%[^0]%', COL+'.'), LEN(COL))
FROM TBL
The are no problems, only solutions. --John Lennon
I'd say its purpuse is to strip leading zeros from (I'm guessing) a numeric string in the col column, i.e. '000001200' becomes '1200'. This is useful for example when processing a flat file that isn't a csv, i.e. fields are only defined by position and length within the record. Or any other string that contains a numeric value that has leading zeros, really.
Adding the '.' to the input value ensures that the PATINDEX always returns a non-zero value, i.e. if the input value is a string that consist of only zeros, the PATINDEX will return the length of the column/string. This will in turn ensure that the substring function will return an empty string in that case.
July 13, 2022 at 5:25 pm
(tbd)
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".
July 13, 2022 at 5:25 pm
Awesome! Thank you soooo much for that explanation. I was half right
The are no problems, only solutions. --John Lennon
September 1, 2022 at 6:03 am
The regular expression here '%[^0]%' starts by looking for the first non zero in the string (col+'.')
120. --> 120
0150. --> 150
The ^ is a negation operator (meaning NOT) when inside the brackets. Means 'Starts with' when outside.
Patindex is a SQL representation of Regular Expressions (seen in Unix/Linux).
----------------------------------------------------
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy