SQL convert

Question

Post reply

SQL convert

newbieuser

SSCrazy Eights

Points: 9423
More actions
March 21, 2016 at 10:48 am

#314219

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

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

shaneoneillis SSCommitted Points: 1750 More actions · Answer 1

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'

newbieuser SSCrazy Eights Points: 9423 More actions · Answer 2

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

shaneoneillis SSCommitted Points: 1750 More actions · Answer 3

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.

newbieuser SSCrazy Eights Points: 9423 More actions · Answer 4

I should have figured that on my own 🙂 Thank you so much!

shaneoneillis SSCommitted Points: 1750 More actions · Answer 5

No worries, we're all still learning and at least you'll have this tool under your belt now 🙂

Luis Cazares SSC Guru Points: 183698 More actions · Answer 6

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;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

shaneoneillis SSCommitted Points: 1750 More actions · Answer 7

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:

newbieuser SSCrazy Eights Points: 9423 More actions · Answer 8

newbieuser

SSCrazy Eights

Points: 9423

March 23, 2016 at 9:06 am

#1867119

Thank you for the answers