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

x
?
Solved

SQL Change first 0 in a string with O

Posted on 2011-05-06
12
Medium Priority
?
259 Views
Last Modified: 2012-05-11
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
Comment
Question by:kwitcom
  • 5
  • 3
  • 2
  • +1
12 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 35706375
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 35706384
sorry, charindex,  not instr
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35706422
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:kwitcom
ID: 35706437
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
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 35706543
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
 

Author Comment

by:kwitcom
ID: 35706631
@zero_index = 1
charindex("1",@string) = 0

Sample String: '023BG'
Does not work.
0
 

Author Comment

by:kwitcom
ID: 35706937
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
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 2000 total points
ID: 35707300
>>> IF @zero_index = 1 OR (@zero_index < @num_index)

this should be


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


0
 
LVL 9

Expert Comment

by:anillucky31
ID: 35707453
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
 
LVL 9

Expert Comment

by:anillucky31
ID: 35707456
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
 
LVL 41

Expert Comment

by:Sharath
ID: 35707600
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
 
LVL 41

Expert Comment

by:Sharath
ID: 35708058
Seems like you did not even try other suggestions. Anyways let's move on...
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ready to get certified? Check out some courses that help you prepare for third-party exams.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

579 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question