Use of Concatenate Function

[VIJSQL01]

hi,

I am struggling to add a prefix to one of the column value in my table. The scenario goes like this.
I have values in a column, which are 4 as well as 5 digits. The values which have 4 digits should be added with '0' as prefix.
I already tried out following way, but it turned out be wrong. Please help me with this. Also i am surprised with the error it thrown.
The error which i am getting is that -- 'Concat is not a recognised built in function'..PLEASE HELP.

SQL:
[highlight #EF2929]

select case when LEN([FIPS Code])=4 THEN CONCATENATE('0',[FIPS Code])
ELSE END AS [FIPS Code]
From RT_County_Master

[/highlight]

Error:

[highlight #EF2929]

Msg 195, Level 15, State 10, Line 1
'CONCATENATE' is not a recognized built-in function name.

[/highlight]

 

[bborissov]

select RIGHT('00000'+ CAST([FIPS Code] as varchar(5), 5) AS [FIPS Code]
From RT_County_Master

Borislav Borissov
VFP9 SP2, SQL Server

 

[VIJSQL01]

WOW!..thanks for a quick reply.

Well, unfortunately i am still getting a syntax error when i tried to execute the above mentioned query.

Error:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ','.

But, i just want to reiterate that, i just need to add only one Zero as prefix. Not really 5 zeros.

Thanks once again for your help on that. I greatly appreciate that!
~VIJSQL~

 

[fredericofonseca]

boris had a small typo

select RIGHT('00000'+ CAST([FIPS Code] as varchar(5)[highlight #CC0000])[/highlight], 5) AS [FIPS Code]
From RT_County_Master

and the above right('00000' + ... ,5) is ensuring that even if your "Fips code" has less than 4 chars it will always return 5 with leading zeros


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[VIJSQL01]

Thank you Thank you Thank you......

This is working..Great! You guys made my day.

 

[VIJSQL01]

Also, at the same time i would like understand how the Cast function works here. Here the Code

right('00000' + ... ,5)

how exactly it works and what is the main purpose of using the CAST function here?
Please throw some light on this, if possible.

 

[bborissov]

Cast the field as varchar is to remove all trailing spaces and get the real length of the string.
So if you have record like this:
[tt]
'3ch '
[/tt]

you must get:
[tt]
'3ch'
[/tt]
then:

print RIGHT('00000' + CAST(TheField as varchar(5)), 5) -- '003ch'

Borislav Borissov
VFP9 SP2, SQL Server