Any Help with SYNTAX

Transact-SQL
1

Here is the syntax

SELECT CASE WHEN patindex('%.%.%.%',@@version) > 0 THEN cast(substring(@@version, patindex('%.%.%.%',@@version)-2,charindex('.',ltrim(substring(@@version, patindex('%.%.%.%',@@version)-2,8)))) as int) ELSE 0 END"

SELECT CASE WHEN patindex('%.%.%.%',@@version) > 0 THEN cast(substring(@@version, patindex('%.%.%.%',@@version)-2,charindex('.',ltrim(substring(@@version, patindex('%.%.%.%',@@version)-2,8)))) as int) ELSE 0 END

Error I get

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the nvarchar value '15.' to data type int.

Thx yall

2

There is a . After 15 that's why it won't convert to int

What sort of solution did you have in mind

If it's just 15 it will convert

Your substring lengths may need to be adjusted

You can use try_convert

Or you can replace the dot. .and other letters with empty string . Just keep numbers in string and convert

All depends on how your data is going to be

:stuck_out_tongue_winking_eye:

3

So what should be the correct syntax. Still having difficulty....

4

It's giving error because of data

Not syntax

5

So what's the fix

6

We have no idea since we cannot see your data - or your expected results. Can you provide a sample in the form of a table and insert statement(s) of example data and the expected results from that data?

7

Need to subtract 1 from the CharIndex statement to remove the decimal

SELECT CASE WHEN patindex('%.%.%.%',@@version) > 0 THEN 
			cast(substring(@@version, patindex('%.%.%.%',@@version)-2,charindex('.',ltrim(substring(@@version, patindex('%.%.%.%',@@version)-2,8))) - 1) as int) 
	ELSE 0 
	END