Set the fixed length of Post Code

1

Can you please advise me how to generate the outcome where the total length should be 8 character including the spaces
In the output results the post code "A1 3FD" should have 3 spaces , and
for post code "BC1 0N'F" shoud show 2 spaces. and for "GU26 9EL" will only 1 space as already in the source data as shown below
Hope make sense
thanks


drop table postcode;
create table postcode 
(PostCode VARCHAR(10)
)
insert into postcode values ('GU26 9EL');
insert into postcode values ('A1 3FD');
insert into postcode values ('BC1 0N'F);

select 
postcode
,lEN(Postcode) as PostCode_Len
,CHARINDEX(' ',Postcode) AS SpacePosition
,CASE WHEN CHARINDEX(' ',Postcode) = 3 Then substring(postcode,1,2) ELSE 0 END
from postcode;

Getting the following error for above query
Conversion failed when converting the varchar value 'B1' to data type int.

2

CASE expression returns only 1 data type.

In your query

CASE WHEN CHARINDEX(' ',Postcode) = 3 Then substring(postcode,1,2) ELSE 0 END

substring() returns a string while the ELSE 0 returns integer. When you have 2 different data type, SQL Server will implicitly convert one to the other according to the data type precedence.

For your query, integer has higher precedence and hence it is attempting to convert B1 to integer

One way is to use ELSE '0' instead of ELSE 0

3

Thanks a lot Khtan, you resolve the issue some time a tiny silly mistake confuse me
finally came up with the answer thanks again and appreciate your help.
Final qery below

select
x1. postcode
,len(x1.Postcode) as Original_PostCode_Length
,x1.Derive_Postcode 
,LEN(x1.Derive_Postcode)
from
(
select 
postcode
,lEN(Postcode) as PostCode_Len
,CHARINDEX(' ',Postcode) AS SpacePosition
,CASE 
	WHEN CHARINDEX(' ',Postcode) = 3 Then CONCAT(substring(postcode,1,2),'   ',substring(postcode,4,3)) ---spaces
	WHEN CHARINDEX(' ',Postcode) = 4 Then CONCAT(substring(postcode,1,3),' ',substring(postcode,4,4))    -1 space
ELSE Postcode 
END AS Derive_Postcode
from postcode
) x1