How to get data after a 2 string separators in a select (SQL)?

Question

How to get data after a 2 string separators in a select (SQL)?

Navigation

#1 by (1 votes)

2

I have a string where I store day, month and year, no field is required, and I separate the fields by a pipe .

If you put the day only via "25", if you have placed the day and month or only the month will be respectively "25 | 12" or "| 12".

The same logic for year we can conclude is that if you have year the string will have 2 pipes.

I needed to get the value of the year, ie the value after the 2nd pipe that can even be empty, if the user has deleted. The user can type 1 or 2 characters for day or month and up to 3 characters for the year, so I can not use substring counting the characters.

Does anyone know how I can do this?

sql

asked by anonymous 22.08.2016 / 22:53

1 answer

Pick view position on screen and apply to another view How to connect a MySql database with QT over a LAN?

score 1 · Answer 1

You can use the RIGHT function that returns the last element (s) (s) of the string and make a case to check if your character is in the last position.

declare @teste table
(
  dha varchar(20)
)

insert into @teste values 
('25'),('25|'),('||'),('||16'),('||016'),('|02|016'),('|02|16'),('01||'),('01|02|'),('01|02|16'),('01|02|016')


select CHARINDEX('|', dha),  
case 
    when  (CHARINDEX('|', dha) = 0) or (RIGHT(dha, 2) = '||' or RIGHT(dha, 1) = '|') then ''
    else RIGHT(dha, 2)
end

from @teste