By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,265 Members | 1,333 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,265 IT Pros & Developers. It's quick & easy.

replacing the 8th character in a varchar string

P: 3

I have problem, where I need to replace the 8th character in a varchar string eg 002556-108823-AN-02 ie I need to change the 1 to 3. (and 2s to 4s in other strings eg 040598-20066-AN-02)

I rarely use SQL but I know I'll need the update and replace function but I don't know how to specify the specific character, especially as there maybe other characters of the same type in the string, but I only need the eighth one in the string changed

Any help would be hughly appreciated
Sep 23 '09 #1
Share this Question
Share on Google+
2 Replies

code green
Expert 100+
P: 1,726
Wrong answer given to post
Sep 24 '09 #2

Expert 100+
P: 1,134
From Help documents.
Replaces all occurrences of the second given string expression in the first string expression with a third expression.

This example replaces the string cde in abcdefghi with xxx.
SELECT REPLACE('abcdefghicde','cde','xxx')
Replace dosn't work with character position.
If you must replace a character at a particular position in a string then I don't know of any SQL fuction that specifically does that. If someone does know then I for 1 am interested.

Here is my clunky method to replace the 8'th character of a string in a query

Expand|Select|Wrap|Line Numbers
  1. select left(str,7) + 'Replacement char'  + right(str,len(str)-8) as str
In the particular example that you provide then multiplying the 8th character by 2 will change the 1 to a 2 and the 2 to a 4
Expand|Select|Wrap|Line Numbers
  1. select left(str,7) + convert(char(1),convert(int,substring(str,8,1))*2) + right(str,len(str)-8) as str
If not always 1 or 2 then you may need to do successive runs of your update query. Once for each number in the 8th position.
Or use CASE WHEN to make decisions based on the value of the 8th character

You can always make a UDF
Expand|Select|Wrap|Line Numbers
  1. CREATE FUNCTION fnReplaceCharAtPos(@Str varchar(8000),@Pos int, @Chr char(1))
  2. RETURNS varchar(8000) AS  
  3. BEGIN 
  4. declare @Res varchar(8000)
  5. set @Res=left(@Str,@Pos-1) + @Chr  + right(@Str,len(@Str)-@Pos)
  6. return @Res
  7. END
and call it in a query with
Expand|Select|Wrap|Line Numbers
  1. select dbo.fnReplaceCharAtPos(str,8,'Z')
  2. from YourTable
Sep 24 '09 #3

Post your reply

Sign in to post your reply or Sign up for a free account.