PATINDEX question
-
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 TBLThe 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.
-
(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".
-
Awesome! Thank you soooo much for that explanation. I was half right
The are no problems, only solutions. --John Lennon
-
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