asked on SQL View Syntax
I have a view (Not created by me) :-
SELECT UPPER(SUBSTRING([Stock Code], PATINDEX('%[^0]%', [Stock Code]), LEN([Stock Code]))) AS StockCode, [Stock Code] AS StockCode2
FROM dbo.[tbl_My_Table]
WHERE ([Stock Code] = '02-1600HF')
It returns:
StockCode StockCode2 (I added this column)
2-1600HF 02-1600HF
This view feeds into a BI system and I don't want to break it, does anyone know what "PATINDEX('%[^0]%'" does and why it would be used?
I need to keep the leading Zeros 'StockCode' does not have them.
SELECT UPPER(SUBSTRING([Stock Code], PATINDEX('%[^0]%', [Stock Code]), LEN([Stock Code]))) AS StockCode, [Stock Code] AS StockCode2
FROM dbo.[tbl_My_Table]
WHERE ([Stock Code] = '02-1600HF')
It returns:
StockCode StockCode2 (I added this column)
2-1600HF 02-1600HF
This view feeds into a BI system and I don't want to break it, does anyone know what "PATINDEX('%[^0]%'" does and why it would be used?
I need to keep the leading Zeros 'StockCode' does not have them.
SQL* Views
Last Comment
Lasse Bodilsen
ASKER
Hi Lasse there are no occurrences of '^0' in the source data.
I'm trying to decide if I just use 'UPPER([Stock Code])' then it will still work.
I'm trying to decide if I just use 'UPPER([Stock Code])' then it will still work.
Hi
The ^ is a string operator, if you read the link I posted:
So basically in your query it locates the first variable that is non zero. eg. 2 in your example. and that is why it omits the zero in the output.
The ^ is a string operator, if you read the link I posted:
D. Using complex wildcard expressions with PATINDEX
The following example uses the [^] string operator to find the position of a character that is not a number, letter, or space.SELECT position = PATINDEX('%[^ 0-9A-z]%', 'Please ensure the door is locked!');
position -------- 33
So basically in your query it locates the first variable that is non zero. eg. 2 in your example. and that is why it omits the zero in the output.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
I'm going to change it and test, wish me luck!
Best of luck :)
https://docs.microsoft.com/en-us/sql/t-sql/functions/patindex-transact-sql?view=sql-server-ver15
So in your case it points to the starting position of the first occurence of: ^0
Does that make sence?