Stanley Lai
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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_
select @b = substring(@ab,1,@num_chars
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 )
upper( substring( input, char_length( input ) - 3, 1 ) ) + substring( input, 1, char_length( input ) - 4 )
upper( substring( input, char_length( input ) - 4, 1 ) ) + substring( input, 1, char_length( input ) - 4 )