Link to home
Start Free TrialLog in
Avatar of Stanley Lai
Stanley LaiFlag for Hong Kong

asked on

substring and charindex

How can i use the sql to the following in order
1. trim out the last 3 charactor
2. then get the last character and converted into upper letter and place to variable A
3. trim the last 4 character and place to B
4. A + B

e.g 1
input = 40yBEY
output
A = Y
B = 40

output = A + B = Y40


e.g 2
input = 2yBEY
output
A = Y
B = 2

output = A + B = Y2
ASKER CERTIFIED SOLUTION
Avatar of Jan Franek
Jan Franek
Flag of Czechia 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
And 1. + 2. can be shortened to - get upper case of 4-th last character so you can do it also this way:

upper( substring( input, char_length( input ) - 4, 1 ) ) + substring( input, 1, char_length( input ) - 4 )
Avatar of smaligap
smaligap

This is a crude test i tried. it works and maybe you can fine tune it. hope it helps

declare @ab char(6),@a char(3),@b char(3),@abc char(6),@num_chars int
select @ab = "40yBEY"
select @num_chars = char_length(@ab)
select @a = right(substring(@ab,(@num_chars - 2),3),1)
select @b = substring(@ab,1,@num_chars - 4)
select @abc= rtrim(@a) + @b
select @abc

NOTE : You will have to declare the length your output variable (@abc in this case) to be the sum of the A + B variable lengths.
Ooops - mistake:

upper( substring( input, char_length( input ) - 3, 1 ) ) + substring( input, 1, char_length( input ) - 4 )