Replace character

gregalb · #1 (**permalink**) July 10th, 2007, 10:45 PM

Hi I have data in a field

38307*412*11*
38703*412*11*A*
38703*412*11*AA*

I would like to remove the first * and replace the rest of the * with . and have no * at the end - how can I achieve this?

The result must be

38307/412.11
38703/412.11.A
38703/412.11.AA

Thanks Guys

Greg

happygv · #2 (**permalink**) July 11th, 2007, 01:35 AM

Is that the length of the first segement(5 chars) gonna be same for all the rows?

_________________________
- Vijay G
Strive for Perfection

gregalb · #3 (**permalink**) July 11th, 2007, 03:42 AM

Hi Vijay

Yes the first segment will be 5 (char) in length for all rows

happygv · #4 (**permalink**) July 11th, 2007, 05:23 AM

Try this. Anyways string manipulation is slow in sql.

Code:

DECLARE @var VARCHAR(100)
SET @var = '38703*412*11*AA*'
SELECT Left(Left(@var, 5) + '/' + Right(Replace(@var, '*', '.'), Len(@var) - 6 ), Len(@var) - 1)

Hope that helps.
Cheers.

_________________________
- Vijay G
Strive for Perfection

gregalb · #5 (**permalink**) July 11th, 2007, 04:38 PM

Thank vijay

I would like to update my table not just select from it

Thanks
Greg

Jeff Moden · #6 (**permalink**) July 11th, 2007, 06:42 PM

So, use the formula that Vijay provided in an UPDATE...

--Jeff Moden

happygv · #7 (**permalink**) July 12th, 2007, 01:14 AM

Yes Greg, then use the column name in place of @var in the UPDATE statement and you don't need the DECLARE and SET statements.

_________________________
- Vijay G
Strive for Perfection

Need to download code?

Navigation