Windows Forum

Ask something
Question Locked

T-SQL Question

0 helpful
6 replies

by slayer_

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

My problem is simple. The first character of a piece of data in a column may be invalid. I need a script to fix it. Preferably not using a stored procedure.

My first idea was this
Update Service10
Set LN_SmString2 = ('M' + RIGHT((Select LN_SmString2 From Service10),Len(Select LN_SmString2 From Service10) - 1)))
Where LEFT(LN_SmString2,1) <> 'M' or LEFT(LN_SmString2,1) <> 'S' or LEFT(LN_SmString2,1) <> 'D'

I've bolded the specific problem, how do I tell it which row to grab the data, the select and the update need to be looking at the same row for this to work.

If there is an easy solution to this, I'd like to know.

Otherwise, my solution would be using a variable and a curser. That should work fine EXCEPT I only know how to use variables and cursors in stored procs. I again, don't want a stored proc, just a single use script. Online help is not very helpful for some reason with this issue, so help would be appreciated.

The jyst of this script is this.

If the first characters is not M, S, or D, then make it M. All characters after that must remain the same. It is a 30 character CHAR and each character represents something to the system.

6 replies

by slayer_

As it shows you can only have varibles within stored procs.

by tony hopkinson

Update Service10
Set LN_SmString2 = 'M' + SubString(LN_SmString2,2)
Where LN_SmString2 Not Like '[MSD]%'

It's not the fastest thing you'll ever see, but you could beef it up with a cleanedup column

Update Service10
Set LN_SmString2 = 'M' + SubString(LN_SmString2,2), CleanedUp = 1
Where CleanedUp =0 and LN_SmString2 Not Like '[MSD]%'

Or perhaps better still is MSD means something

Set SmID = SubString(LN_SmString2,1,1), LN_SmString2 = SubString(LN_SmString2,2)
Where SM_ID is NULL and LN_SmString2 Like '[MSD]%'
followed by

Set SM_ID = 'M', LN_SMString2 = SubString(LN_SmString2,2) Where SM_ID is null

Put an index on SM_ID and it will go like hot snot.

PS you can use cursor and vars is in scripts

It's passing and return parameters to a script where you don't get much help.

HtHs

Substring (SomeString,2) returns the 2nd char to the end

Substring(SomeString(2,3) returns up to 3 chars starting at and including the second

Like '[MSD]%' returns anything starting with M, S Or D

e.g. no % sign would only return stuff that equalled M, S or D

In an update statement the record currently being operated on is available for use as input to the manipulation so you can do suff like

MyNumber
1
2
3
4

Update MyNumbers Set MyNumber = MyNumber + 1

and end up with

MyNumber
2
3
4
5

by slayer_

That one piece of knowledge, that you could use the column your updating, as an input to the update, would have solved this. thank you very much :).

I wish I could give you 10 thumbs up :)

by slayer_

Update Service10
Set LN_SmString2 = 'M' + SubString(LN_SmString2,2,len(LN_SmString2)-1)
Where LN_SmString2 Not Like '[MSD]%' and len(LN_SmString2) > 0
GO

Needed extra parm for length in substring, and this then required me to check for blanks.

This discussion is closed to new replies.