SQL query help

have have column 1 that has values like this:

abc 123
defg 15
we 24

I need a SQL statement that will return the values so that
1) the space is removed
2) all two digit numbers are prepended with a zero

thus the resulting query SQL statement is:
abc123
defg015
we024

I am using SQL server 7.0 still unfortunately

thanks
sopheak
sopheakAsked:
Who is Participating?
 
rafranciscoCommented:
Now wonder it didn't work, it was char and not varchar.  This should take care of it now:

DECLARE @a char(100)
set @a = 'defg 15        '
SELECT replace(replace(rtrim(@a), RIGHT(rtrim(@a), charindex(' ', reverse(rtrim(@a))) - 1), RIGHT('000' + RIGHT(rtrim(@a), charindex(' ', reverse(rtrim(@a))) - 1), 3)), ' ', '')
0
 
rafranciscoCommented:
Try this:

DECLARE @a varchar(100)
set @a = 'abc 123'
SELECT replace(replace(@a, RIGHT(@a, charindex(' ', reverse(@a)) - 1), RIGHT('000' + RIGHT(@a, charindex(' ', reverse(@a)) - 1), 3)), ' ', '')

Just change @a to your column name.
0
 
sopheakAuthor Commented:
rafranciso,

that worked at removing the space, but I need the second part.  If a number is two digit, then add a zero

so defg 15 would be defg015

thanks
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
rafranciscoCommented:
I tried that and it gave me the result you want.
0
 
Scott PletcherSenior DBACommented:
Please try this:


DECLARE @value VARCHAR(30)

SET @value = 'defg 15'

SELECT REPLACE(CASE WHEN @value LIKE '%[0-9][0-9][0-9]%' THEN @value
      ELSE STUFF(@value, PATINDEX('%[0-9%]', @value) - 1, 0, '0') END, ' ', '')
0
 
Scott PletcherSenior DBACommented:
CORRECTION (typo on second PATINDEX):

SELECT REPLACE(CASE WHEN @value LIKE '%[0-9][0-9][0-9]%' THEN @value
     ELSE STUFF(@value, PATINDEX('%[0-9]%', @value) - 1, 0, '0') END, ' ', '')


For a column, naturally just replace @value with the column name :-) .

This code assumes only three+ or two; if needed, it can easily be adjusted to add two zeros instead of 1 for a value such as "abc 1".
0
 
sopheakAuthor Commented:
I'm sorry,  the colum is a char, not varchar.  

If you change your decalare to
DECLARE @a char(100)
it does not work correctly.

0
 
Scott PletcherSenior DBACommented:
Mine work on either as it was originally coded :-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.