Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Update Part of a SQL field with charindex Expand / Collapse
Author
Message
Posted Monday, June 23, 2014 12:45 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, July 11, 2014 10:05 AM
Points: 4, Visits: 10
Hello,

I have a field and the data is in the format below:

12-FLA-2-02

I am trying to update just the first part of the data before the first dash which is 12 to another field that is a single numeric value.

update table set table.field = LEFT(table.field, CHARINDEX('-', table.field)-1) where table.field is null


I am trying to manipulate this script below, but I cannot figure out how to update just the 12 before the dash.

Thanks

Larry

Post #1585236
Posted Monday, June 23, 2014 1:15 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, December 11, 2014 5:11 PM
Points: 2,717, Visits: 3,857
Read up on STUFF .. http://msdn.microsoft.com/en-us/library/ms188043.aspx

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg
Post #1585254
Posted Monday, June 23, 2014 2:39 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, July 11, 2014 10:05 AM
Points: 4, Visits: 10
Thank You!!!

That STUFF function did exactly what I needed it to do.

Thanks Again.

Post #1585310
Posted Tuesday, June 24, 2014 1:52 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 3:09 AM
Points: 314, Visits: 2,530
You can use REPLACE function as well.
declare @str as table( val varchar(20))
insert @str
select '12-FLA-2-02'
declare @update varchar(20)='11111'

select * from @str
update @str set val=replace(val,LEFT(val, CHARINDEX('-', val)-1) ,@update)

select * from @str

Post #1585409
Posted Tuesday, June 24, 2014 2:04 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:43 AM
Points: 6,890, Visits: 14,254
Sachin Nandanwar (6/24/2014)
You can use REPLACE function as well.
declare @str as table( val varchar(20))
insert @str
select '12-FLA-2-02'
declare @update varchar(20)='11111'

select * from @str
update @str set val=replace(val,LEFT(val, CHARINDEX('-', val)-1) ,@update)

select * from @str



You can, but I wouldn't recommend it:
declare @str as table( val varchar(20))
insert @str select '12-FLA-2-02'
insert @str select '12-FLA-2-12'
declare @update varchar(20)='11111'

select * from @str
update @str set val=replace(val,LEFT(val, CHARINDEX('-', val)-1) ,@update)

select * from @str



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1585411
Posted Tuesday, June 24, 2014 2:42 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 3:09 AM
Points: 314, Visits: 2,530

You can, but I wouldn't recommend it:
declare @str as table( val varchar(20))
insert @str select '12-FLA-2-02'
insert @str select '12-FLA-2-12'
declare @update varchar(20)='11111'

select * from @str
update @str set val=replace(val,LEFT(val, CHARINDEX('-', val)-1) ,@update)

select * from @str



Yes makes sense.Thanks for pointing that out..
Post #1585415
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse