• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 261
  • Last Modified:

SQL Change first 0 in a string with O

I need to write a FUNCTION that I can hand in a CHAR(30) that will change the string and return CHAR(30)

Only Need to change first 0(number) to a O(letter).

BFGI04560N2F           = BFGIO4560N2
BFGI04560W3            = BFGIO4560W3
GTS0240A1B             = GTSO240A1
023BG                        = O23

R-BFGI04560W3        = BFGIO4560N2

CREATE FUNCTION [dbo].[Test_New] (@string CHAR(30))
RETURNS VARCHAR(30)
AS
  BEGIN
      DECLARE @ErrNum  INT,
              @char    VARCHAR(30),
              @stringloc INT,
              @Tstring   VARCHAR(30)

      SET @Tstring = ''
      SET @ErrNum = 0

      IF LEFT(@string, 2) = 'R-'
        BEGIN
            SET @string = Substring(@string, 3, 30)
        END

      SELECT @Tstring = CASE
                        when isnumeric(substring(@string,len(@string),1)) = 1 then @string 
                        when isnumeric(substring(@string,len(@string)-1,1)) = 1 then substring(@string,1,len(@string)-1)
                        when isnumeric(substring(@string,len(@string)-2,1)) = 1 then substring(@string,1,len(@string)-2)
                        when isnumeric(substring(@string,len(@string)-3,1)) = 1 then substring(@string,1,len(@string)-3)
                        when isnumeric(substring(@string,len(@string)-4,1)) = 1 then substring(@string,1,len(@string)-4)
                        when isnumeric(substring(@string,len(@string)-5,1)) = 1 then substring(@string,1,len(@string)-5)
                      END
                      
      RETURN @Tstring

      MYERROR:

      BEGIN
          RETURN NULL
      END
  END

GO

Open in new window

0
kwitcom
Asked:
kwitcom
  • 5
  • 3
  • 2
  • +1
2 Solutions
 
sdstuberCommented:
your examples don't match your description

you have other changes beside the 0 to O replacement.

but to solve the specific question...

INSTR to find the first 0
substr everything prior to that position append O
then append everything after that position

then apply all other changes
0
 
sdstuberCommented:
sorry, charindex,  not instr
0
 
sdstuberCommented:
it might look something like this...

if charindex("0",@string) > 0
begin
      SET @string = substring(@string, 1, charindex("0",@string)-1)
                        + "O"
                        + substring(@string, charindex("0",@string)+1,30)
end
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.

 
kwitcomAuthor Commented:
Sorry I needed the other changes to happen as well.

I just got more information and there is a little more to it now.  Only change the 0 to O when it's the first number in the string.

So T300BGD3R needs to be T300BGD3 and the 0 is not changed but T000F3D would change to TO00F3 but only the first 0.
0
 
sdstuberCommented:
declare a variable to store the zero index

then add this to your function

SET @zero_index = charindex("0",@string)

if @zero_index  > 0
and @zero_index  < charindex("1",@string)
and @zero_index < charindex("2",@string)
and @zero_index  < charindex("3",@string)
and @zero_index  < charindex("4",@string)
and @zero_index  < charindex("5",@string)
and @zero_index  < charindex("6",@string)
and charin@zero_index < charindex("7",@string)
and @zero_index  < charindex("8",@string)
and @zero_index < charindex("9",@string)
begin
      SET @string = substring(@string, 1, @zero_index -1)
                        + "O"
                        + substring(@string, @zero_index +1,30)
end
0
 
kwitcomAuthor Commented:
@zero_index = 1
charindex("1",@string) = 0

Sample String: '023BG'
Does not work.
0
 
kwitcomAuthor Commented:
This is what I did:
SET @zero_index = charindex ('0', @string)
      SET @num_index = 30

SELECT @num_index =
          CASE
             WHEN @num_index > charindex ('1', @string) and charindex ('1', @string) > 0 THEN charindex ('1', @string)
             WHEN @num_index > charindex ('2', @string) and charindex ('2', @string) > 0 THEN charindex ('2', @string)
             WHEN @num_index > charindex ('3', @string) and charindex ('3', @string) > 0 THEN charindex ('3', @string)
             WHEN @num_index > charindex ('4', @string) and charindex ('4', @string) > 0 THEN charindex ('4', @string)
             WHEN @num_index > charindex ('5', @string) and charindex ('5', @string) > 0 THEN charindex ('5', @string)
             WHEN @num_index > charindex ('6', @string) and charindex ('6', @string) > 0 THEN charindex ('6', @string)
             WHEN @num_index > charindex ('7', @string) and charindex ('7', @string) > 0 THEN charindex ('7', @string)
             WHEN @num_index > charindex ('8', @string) and charindex ('8', @string) > 0 THEN charindex ('8', @string)
             WHEN @num_index > charindex ('9', @string) and charindex ('9', @string) > 0 THEN charindex ('9', @string)
          END

IF @zero_index = 1 OR (@zero_index < @num_index)
   BEGIN
      SET @string =
               substring (@string, 1, @zero_index - 1)
             + 'O'
             + substring (@string, @zero_index + 1, 30)
   END

Open in new window

0
 
sdstuberCommented:
>>> IF @zero_index = 1 OR (@zero_index < @num_index)

this should be


IF @zero_index > 0 AND (@zero_index < @num_index)


0
 
anillucky31Commented:
This will work for you.

DECLARE @InputString VARCHAR(100)
DECLARE @OutputString VARCHAR(100)
DECLARE @TempString VARCHAR(100)
DECLARE @Length INT
DECLARE @NumberBeforeZero INT

SET @InputString = 'T300BGD3'
SET @OutputString  = @InputString
SET @NumberBeforeZero = 0

IF(CHARINDEX('0', @InputString) > 0)
BEGIN
      SET @TempString = SUBSTRING(@InputString, 1, CHARINDEX('0', @InputString)-1)      
      SET @Length = LEN(@TempString)
      
      WHILE @Length > 0
            BEGIN
                  
                  IF (SUBSTRING(@TempString, 1, 1 ) = CHAR(49))
                  BEGIN
                        SET @NumberBeforeZero = 1
                  END
                  ELSE IF(SUBSTRING(@TempString, 1, 1 ) = CHAR(50))
                  BEGIN
                        SET @NumberBeforeZero = 1
                  END
                  ELSE IF(SUBSTRING(@TempString, 1, 1 ) = CHAR(51))
                  BEGIN
                        SET @NumberBeforeZero = 1
                  END
                  ELSE IF(SUBSTRING(@TempString, 1, 1 ) = CHAR(52))
                  BEGIN
                        SET @NumberBeforeZero = 1
                  END
                  ELSE IF(SUBSTRING(@TempString, 1, 1 ) = CHAR(53))
                  BEGIN
                        SET @NumberBeforeZero = 1
                  END
                  ELSE IF(SUBSTRING(@TempString, 1, 1 ) = CHAR(54))
                  BEGIN
                        SET @NumberBeforeZero = 1
                  END
                  ELSE IF(SUBSTRING(@TempString, 1, 1 ) = CHAR(55))
                  BEGIN
                        SET @NumberBeforeZero = 1
                  END
                  ELSE IF(SUBSTRING(@TempString, 1, 1 ) = CHAR(56))
                  BEGIN
                        SET @NumberBeforeZero = 1
                  END
                  ELSE IF(SUBSTRING(@TempString, 1, 1 ) = CHAR(57))
                  BEGIN
                        SET @NumberBeforeZero = 1
                  END
                  
                  
                  SET @TempString = SUBSTRING(@TempString, 2, @Length )
                  
                  SET @Length = @Length - 1
            END
      
      
END

 

 IF (@NumberBeforeZero = 0)
      SET @OutputString = SUBSTRING(@InputString, 1, CHARINDEX('0', @InputString ) - 1) + 'O' + SUBSTRING(@InputString, CHARINDEX('0', @InputString ) +1,  LEN(@InputString))
      
      
      SELECT  @OutputString
      
0
 
anillucky31Commented:
i am pasting it as code
DECLARE @InputString VARCHAR(100)
DECLARE @OutputString VARCHAR(100)
DECLARE @TempString VARCHAR(100)
DECLARE @Length INT
DECLARE @NumberBeforeZero INT

SET @InputString = 'T300BGD3'
SET @OutputString  = @InputString
SET @NumberBeforeZero = 0

IF(CHARINDEX('0', @InputString) > 0)
BEGIN
	SET @TempString = SUBSTRING(@InputString, 1, CHARINDEX('0', @InputString)-1)	
	SET @Length = LEN(@TempString)
	 
	WHILE @Length > 0
		BEGIN
			
			IF (SUBSTRING(@TempString, 1, 1 ) = CHAR(49))
			BEGIN
				SET @NumberBeforeZero = 1
			END
			ELSE IF(SUBSTRING(@TempString, 1, 1 ) = CHAR(50))
			BEGIN
				SET @NumberBeforeZero = 1
			END
			ELSE IF(SUBSTRING(@TempString, 1, 1 ) = CHAR(51))
			BEGIN
				SET @NumberBeforeZero = 1
			END
			ELSE IF(SUBSTRING(@TempString, 1, 1 ) = CHAR(52))
			BEGIN
				SET @NumberBeforeZero = 1
			END
			ELSE IF(SUBSTRING(@TempString, 1, 1 ) = CHAR(53))
			BEGIN
				SET @NumberBeforeZero = 1
			END
			ELSE IF(SUBSTRING(@TempString, 1, 1 ) = CHAR(54))
			BEGIN
				SET @NumberBeforeZero = 1
			END
			ELSE IF(SUBSTRING(@TempString, 1, 1 ) = CHAR(55))
			BEGIN
				SET @NumberBeforeZero = 1
			END
			ELSE IF(SUBSTRING(@TempString, 1, 1 ) = CHAR(56))
			BEGIN
				SET @NumberBeforeZero = 1
			END
			ELSE IF(SUBSTRING(@TempString, 1, 1 ) = CHAR(57))
			BEGIN
				SET @NumberBeforeZero = 1
			END
			 
			
			SET @TempString = SUBSTRING(@TempString, 2, @Length ) 
			 
			SET @Length = @Length - 1
		END
	
	
END

 

 IF (@NumberBeforeZero = 0)
	SET @OutputString = SUBSTRING(@InputString, 1, CHARINDEX('0', @InputString ) - 1) + 'O' + SUBSTRING(@InputString, CHARINDEX('0', @InputString ) +1,  LEN(@InputString))
	
	
	SELECT  @OutputString

Open in new window

0
 
SharathData EngineerCommented:
Create a function like this.
CREATE FUNCTION [dbo].[Test_New] (@string CHAR(30))
RETURNS VARCHAR(30)
AS BEGIN
declare @OnlyNumbers varchar(30)
SET @OnlyNumbers=''
 SELECT @OnlyNumbers = @OnlyNumbers + CASE WHEN number LIKE '[0-9]' THEN number ELSE '' END 
   FROM (SELECT SUBSTRING(@string,number,1) AS number 
           FROM (SELECT number FROM master..spt_values 
                  WHERE type = 'p' AND number BETWEEN 1 AND LEN(@string)) as t) as t 
select @string = case when CHARINDEX('0',@OnlyNumbers) = 1 then STUFF(@string,CHARINDEX('0',@string),1,'O') else @string end
return @string
END

Open in new window


Use the function in your code. I have tested with all your sample data here.
create table #table (String varchar(30))
insert #table values ('BFGI04560N2F')
insert #table values ('BFGI04560W3')
insert #table values ('GTS0240A1B')
insert #table values ('023BG')
insert #table values ('R-BFGI04560W3')
insert #table values ('023BG')
insert #table values ('T300BGD3R')

select String,dbo.Test_New(String) ModifiedString from #table
/*
String	ModifiedString
BFGI04560N2F	BFGIO4560N2F                  
BFGI04560W3	BFGIO4560W3                   
GTS0240A1B	GTSO240A1B                    
023BG	O23BG                         
R-BFGI04560W3	R-BFGIO4560W3                 
023BG	O23BG                         
T300BGD3R	T300BGD3R                     
*/
drop table #table

Open in new window

0
 
SharathData EngineerCommented:
Seems like you did not even try other suggestions. Anyways let's move on...
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.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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