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
Medium Priority
259 Views
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
``````
0
Question by:kwitcom
• 5
• 3
• 2
• +1

LVL 74

Expert Comment

ID: 35706375

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

ID: 35706384
sorry, charindex,  not instr
0

LVL 74

Expert Comment

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

Author Comment

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

sdstuber earned 2000 total points
ID: 35706543
declare a variable to store the zero index

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

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

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

Author Comment

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
``````
0

LVL 74

Assisted Solution

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

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

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
``````
0

LVL 41

Expert Comment

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
``````

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
``````
0

LVL 41

Expert Comment

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

## Featured Post

Question has a verified solution.

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