March 21, 2016 at 10:48 am
Hi friends,
I'm converting a complex oracle sql join query to SQL Server. I'm using lpad in oracle, I need an equivalent syntax for this in SQl Server.
I think replicate is the equivalent from a simple google search...
select ....
,lpad(table3.field3,3,'0')||'_'||lpad(table2.field10,3,'0')||'_'||table2.field5 name_yn
from
...
JOIN ..
ON ..
JOIN
...
where 0=0;
Please help..Thank you so much
March 21, 2016 at 11:13 am
Looks good, probably be a bit more helpful if you included what you think the REPLICATE query would go like?
Another option would be to use the RIGHT() function
https://msdn.microsoft.com/en-GB/library/ms177532.aspx"> https://msdn.microsoft.com/en-GB/library/ms177532.aspx
you could use this with your REPLICATE to pad the column out.
SELECT
RIGHT(
-- Put 15 zero's to the front
REPLICATE('0', 15) +
-- Add your data from the column
-- QUOTENAME & REPLACE to wrap your data in ''
-- and concatenate them on
REPLACE(QUOTENAME(table3.field3, ''''), '''', '')
-- Finally, only take the last 10 characters
, 10 )
So for example,
if your field3 in table3 was '123', it would become '0000000123'
or
if it was 'string', it would become '0000string'
March 22, 2016 at 12:18 pm
THank you.
In Oracle, result looks like:
select field10,field5, lpad(t.field10,3,'0')||'_'||t.field5 name_yn from table2 t
field10- number(2)
field5 - varchar2(10)
field10 field5 name_yn
------- ------ -------
1 CARL 001_CARL
2 124-ADJK 002_124-ADJK
10 MARY 010_MARY
I tried the below in SQL server, I get different results...
select field10,field5,right(replicate('0',2) + REPLACE(QUOTENAME(t.field10, ''''), '''', ''),10) + '-" + t.field5 name_yn
from table2 t
field10 - smallint
field5 - varchar(10)
REsult looks like :
field10 field5 name_yn
------- ------ -------
1 CARL 001_CARL
2 124-ADJK 002_124-ADJK
10 MARY 0010_MARY
Basically, the length for field10 in name_yn should be 3. In the last row name_yn should be 010_MARY
Please help..Thanks a lot
March 22, 2016 at 1:15 pm
I tried the below in SQL server, I get different results...
select field10,field5,right(replicate('0',2) + REPLACE(QUOTENAME(t.field10, ''''), '''', ''),10) + '-" + t.field5 name_yn
from table2 t
field10 - smallint
field5 - varchar(10)
REsult looks like :
field10 field5 name_yn
------- ------ -------
1 CARL 001_CARL
2 124-ADJK 002_124-ADJK
10 MARY 0010_MARY
Basically, the length for field10 in name_yn should be 3. In the last row name_yn should be 010_MARY
Fair enough, so for field5 = "Mary", you want the field10 to be "010"?
That bit is enforced by the integer_expression of the RIGHT() function.
RIGHT ( character_expression , integer_expression )
Above you have right([...],''), 10)
So you are saying that you want the column data to be 10 characters long.
Change that to a 3 and that should work out for you.
March 22, 2016 at 1:25 pm
I should have figured that on my own 🙂 Thank you so much!
March 23, 2016 at 3:18 am
No worries, we're all still learning and at least you'll have this tool under your belt now 🙂
March 23, 2016 at 7:59 am
So much trouble adding and removing quotes to a smallint column. Why?
You could add the zeros before converting the value to string.
SELECT field10,
field5,
RIGHT( 1000 + t.field10, 3) + '_' + t.field5 AS name_yn,
RIGHT( POWER(10,3) + t.field10, 3) + '_' + t.field5 AS name_yn
FROM table2 t;
March 23, 2016 at 8:18 am
My bad, at the start I didn't know what the datatype of the column was.
Should have spotted that in the second post.
Would have made it easier to read, understand and explain :ermm:
March 23, 2016 at 9:06 am
Thank you for the answers
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply