T-SQL Question

By Slayer_ ·
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((<Strong>Select LN_SmString2 From Service10</Strong>),Len(<Strong>Select LN_SmString2 From Service10</Strong&gt - 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.

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

Hmm not really

by Slayer_ In reply to Hope this gives you some ...

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

Collapse -

Easier than that

by Tony Hopkinson In reply to T-SQL Question

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.


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


Update MyNumbers Set MyNumber = MyNumber + 1

and end up with


Collapse -

W00t Thanks, I did not know that about updates

by Slayer_ In reply to Easier than that

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 :)

Collapse -

Well I had to make small change, but it worked perfectly

by Slayer_ In reply to Easier than that

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

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

Related Discussions

Related Forums