string function - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

string function

Author

Topic

u2p_inst
Yak Posting Veteran

78 Posts

Posted - 2004-05-29 : 00:50:10

It is possible to changes automaticaly the field description below :

Description
_____________________
AUD0000 XXXXXXXXX
AUD000000 AAAAAAAAA
AUD00000 BBBBBBB
AUD000000 CCCCCCC
AUD000000 DDDDDDD
AUD 00000 EEEEEEEEEE

to Results:

Description
_____________________
AUD000000 XXXXXXXXX
AUD000000 AAAAAAAAA
AUD000000 BBBBBBB
AUD000000 CCCCCCC
AUD000000 DDDDDDD
AUD000000 EEEEEEEEEE

Thanks for your help

oh

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-05-29 : 01:40:45

Maybe this???

Declare @temp table (string nvarchar(20))

Insert Into @Temp Select String = 'AUD0000 XXXXXXXXX'
Insert Into @Temp Select String = 'AUD000000 AAAAAAAAA'
Insert Into @Temp Select String = 'AUD00000 BBBBBBB'
Insert Into @Temp Select String = 'AUD000000 CCCCCCC'
Insert Into @Temp Select String = 'AUD000000 DDDDDDD'
Insert Into @Temp Select String = 'AUD 00000 EEEEEEEEEE'

Select
*,
transformed = left(replace(left(string,charindex(' ',string,5)),' ','') + '000000',9) + right(string,len(string)-charindex(' ',string,5)+1)
From @Temp

Corey Aldebol

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-05-29 : 04:27:39

Do you need to update all the rows to the same description? How about this?

UPDATE TableName SET Description = 'AUD000000'

OS

vganesh76
Yak Posting Veteran

64 Posts

Posted - 2004-05-31 : 08:20:07

If required u can convert the logic into an update statement.

SELECT 'AUD000000' + ' ' + substring(reverse(col1),
1,charindex(' ',reverse(col1)) ) as transformed

FROM
( SELECT 'AUD0000 XXXXXXXXX' as col1 union all
SELECT 'AUD000000 AAAAAAAAA' union all
SELECT 'AUD00000 BBBBBBB' union all
SELECT 'AUD000000 CCCCCCC' union all
SELECT 'AUD000000 DDDDDDD' union all
SELECT 'AUD 00000 EEEEEEEEEE'
) as tb

Enjoy working

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources