Help with update statement - 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

General SQL Server Forums

New to SQL Server Programming

Help with update statement

Author

Topic

rardales
Starting Member

32 Posts

Posted - 2005-12-02 : 11:13:48

Hi, everyone. I was wondering if you can assist me with an update statement. I'm not a pro in SQL, and use it every once in awhile.

Let me explain the situation. In the "insured.insured_name1", we have a good number of names in the "insured_name1" column that begin with a "~ " and then a name. For example, it would read as
"~ Hamburger House" I'd say there are about 500 names like these. What we want to do is remove the tilda symbol and space from all of these names so that it would read as "Hamburger House" instead. I'm sure there's a statement that can update all of these names, but would appreciate your help.

Kristen
Test

22859 Posts

Posted - 2005-12-02 : 11:28:08

Assuming that you only want to update the records where insured_name1 STARTS WITH a tilde:


UPDATE U
SET 
-- SELECT [OldData] = insured_name1,
     [insured_name1] = LTrim(SUBSTRING(insured_name1, 2, LEN(insured_name1)))
FROM insured AS U
WHERE insured_name1 LIKE '~%'

Take a backup first! run from the SELECT ... to the end first, to preview what you are selecting to be changed, and then let the UPDATE run

Kristen

rardales
Starting Member

32 Posts

Posted - 2005-12-02 : 12:17:35

Thanks for your help. Since I also want to get rid of the extra space after the tilde, it would be

WHERE insured_name1 LIKE '~ %'

right?

Kristen
Test

22859 Posts

Posted - 2005-12-02 : 12:27:10

"Since I also want to get rid of the extra space after the tilde ..."

I selected anything begining with a tilde, removed the first character (i.e. the tilde) and then LTrim'd the remainder - which will remove all leading spaces

Note that LEN() will not include TRAILING spaces, so they may get chopped off too ...

Kristen

rardales
Starting Member

32 Posts

Posted - 2005-12-02 : 13:11:07

It worked! You're awesome, Kristen! Thank you for your much needed help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources