replace

Question

replace

venki_1276

Mr or Mrs. 500

Points: 512
More actions
May 13, 2009 at 4:44 am

#155698

hi
replace perticular position char.
ex:
'234$123,N,N'
replace that first 'N' with 'V'
Than'Q

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 1

is the query gonna always replace the first 'N' in your string with V or is your query gonna always replace the 9th position with V or will it always replace the first alpha charater with V?

Thanks

Chris

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 2

here are all 3 example:

DECLARE @String VARCHAR(100)

SET @String = '234$123,N,N'

SELECT STUFF(@String,CHARINDEX('N',@String),1,'V') as [Replace 1st N]

SELECT STUFF(@String,9,1,'V') as [Replace 9th Char]

SELECT STUFF(@String,PATINDEX('%[A-Z]%',@String),1,'V') as [Replace 1st Alpa char]

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

venki_1276 Mr or Mrs. 500 Points: 512 More actions · Answer 3

not always 9th position,the position will change.but from the back side of the string it is in fixed position.

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 4

try this one:

SELECT STUFF(@String,(LEN(@String)-2),1,'V')

Or

SELECT REVERSE(STUFF(REVERSE(@String),3,1,'V'))

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

venki_1276 Mr or Mrs. 500 Points: 512 More actions · Answer 5

venki_1276

Mr or Mrs. 500

Points: 512

May 13, 2009 at 5:01 am

#993556

k fine .

Than'Q

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 6

make sure you understand the code incase the business rules change.

the last post of mine will always replace the 3rd last char...

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 7

Here is another way to do it, here it replaces the 2nd element from a delimted text with user defined fixed value...

; WITH TestData

AS

(

SELECT'234$123,N,N' AS SomeString

UNION ALL

SELECT'1545234$12,ZU,N' AS SomeString

UNION ALL

SELECT'1$2,ZU,NBCX' AS SomeString

)

SELECTSTUFF( SomeString, CHARINDEX( ',', SomeString ) + 1, CHARINDEX( ',', SomeString, CHARINDEX( ',', SomeString ) + 1 ) - CHARINDEX( ',', SomeString ) - 1, 'V' )

FROMTestData

--Ramesh