CODEWITHSUNDEEP
sqlsql-server
nobody
nobody

Reputation: 11090

Get all characters to the left of final special character + 2 characters after the final special character SQL Server

I have a varchar column with version information in the following format 1.x.y.z where x,y,z can be of any length.I need to limit the length of z to only two if it is more than 2.

For example:1.345.23.5dfgdfg should be 1.345.23.5d and 1.345.23.5 should be 1.345.23.5

I have the following sql that gives me the required version but is there a simpler way to achieve this?

declare @s varchar(50)
set @s = '1.345.23.5dfgdfg'
select  left(@s,len(@s) - (charindex('.',reverse(@s))-1)) + substring(right(@s,charindex('.',reverse(@s))-1),0,3)

Upvotes: 2

Views: 37

Answers (3)

SqlZim
SqlZim

Reputation: 38063

condensing some of the logic: 

select left(@s,len(@s)+(3-charindex('.',reverse(@s))))

testing with different values for @s:

create table t (s varchar(50))
insert into t values 
 ('1.345.23.5dfgdfg')
,('1.345.23.5d')
,('1.345.23.3')

select s, left(s,len(s)+(3-charindex('.',reverse(s))))
from t

rextester demo: http://rextester.com/USIW73651

returns: 

+------------------+------------------+
|        s         | (No column name) |
+------------------+------------------+
| 1.345.23.5dfgdfg | 1.345.23.5d      |
| 1.345.23.5d      | 1.345.23.5d      |
| 1.345.23.5       | 1.345.23.5       |
+------------------+------------------+

Upvotes: 1

xQbert
xQbert

Reputation: 35343

Use Parsename() to divide and add back?

Demo

declare @s varchar(50)
set @s = '1.345.23.5dfgdfg'

Select PARSENAME ( @s , 4 ) +'.'+
       PARSENAME ( @s , 3 ) +'.'+
       PARSENAME ( @s , 2 ) +'.'+
       left(PARSENAME ( @s , 1 ),2)

Usually used in referece to IP address or more specifically as it's intended use: Fully qualified objects

1 = Object name 2 = Schema name 3 = Database name 4 = Server name

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270763

If you know that the string always has this format, I would be inclined to use:

select (case when charindex('.', reverse(@s)) < 3 then @s
             else left(@s, len(@s) - charindex('.', reverse(@s)) + 3)
        end)

This is pretty similar to your statement, so you may not think it is simpler.

Upvotes: 1

Related Questions