Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 223
  • Last Modified:

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
0
sopheak
Asked:
sopheak
  • 3
  • 3
  • 2
1 Solution
 
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
 
rafranciscoCommented:
I tried that and it gave me the result you want.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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
 
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
 
Scott PletcherSenior DBACommented:
Mine work on either as it was originally coded :-)
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now