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

Asked

Viewed 501 times

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 the user put only the day via staying "25", if put the day and the month or only the month will stay respectively "25|12" or "|12".

The same logic for year what can be concluded is that if there is year the string will have 2 Pipes.

I needed to take the value of the year, that is 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 it?

sql
  • 1

    Simplify it, create a date field or three fields for day, month, and year. Other than this if you want to complicate use the FUNCTIONS STRING of the respective BD (not informed) to manipulate the string in question.

1 answer

1

You can use the function RIGHT that returns the last(s) element(s) of the string and makes a case to check if its 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

Browser other questions tagged sql

You are not signed in. Login or sign up in order to post.