Link to home
Start Free TrialLog in
Avatar of HKFuey
HKFueyFlag for United Kingdom of Great Britain and Northern Ireland

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.
Avatar of Lasse Bodilsen
Lasse Bodilsen
Flag of Denmark image

PATINDEX:
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?
Avatar of HKFuey

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.
Hi
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!'); 

Open in new window

Here is the result set.

position -------- 33

Open in new window



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
Avatar of Lasse Bodilsen
Lasse Bodilsen
Flag of Denmark image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of HKFuey

ASKER

I'm going to change it and test, wish me luck!
Best of luck  :)