asked on

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

rafrancisco

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.

sopheak

ASKER

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

rafrancisco

I tried that and it gave me the result you want.

Scott Pletcher

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, ' ', '')

Scott Pletcher

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".

sopheak

ASKER

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.

ASKER CERTIFIED SOLUTION

rafrancisco

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Scott Pletcher

Mine work on either as it was originally coded :-)