query help - 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

query help

Author

Topic

anjali5
Posting Yak Master

121 Posts

Posted - 2012-02-14 : 13:25:50

Hello All,

I have this in my table

Col1 Col2

A12346-123123 NULL
AD12319-12323 Null
APR12365-1232 NULL

I needd to get the numbers after the alphabets and before hyphen and update the col2 with those numbers

so I want this

Col1 Col2

A12346-123123 12346
AD12319-12323 12319
APR12365-1232 12365

some of the col1 are empty or null or of less length.

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-14 : 13:38:38

[code]
UPDATE table
SET Col2 = STUFF(LEFT(Col1,CASE WHEN CHARINDEX('-',Col1)>0 THEN CHARINDEX('-',Col1) ELSE LEN(Col1)+1 END -1),1,PATINDEX('%[0-9]%',Col1)-1,'')
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

anjali5
Posting Yak Master

121 Posts

Posted - 2012-02-14 : 13:48:32

I am getting an error
String or binary data might be truncated.

the table has lot of bad data

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-14 : 13:51:21

does Col2 have enough length to hold the data? whats the data type of Col2?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

anjali5
Posting Yak Master

121 Posts

Posted - 2012-02-14 : 13:58:54

nvarchar(5)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-14 : 14:01:30

I'm sure you've some values in col1 where initial numeric part is over 5 characters.
what does below return?


SELECT  COUNT(*) FROM table
WHERE LEN(STUFF(LEFT(Col1,CASE WHEN CHARINDEX('-',Col1)>0 THEN CHARINDEX('-',Col1) ELSE LEN(Col1)+1 END -1),1,PATINDEX('%[0-9]%',Col1)-1,''))>5

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

anjali5
Posting Yak Master

121 Posts

Posted - 2012-02-14 : 14:05:04

i changed the col2 to integer and now i am getting converion failed

and the value is

13867ER021747

is it possible that I only consider the values that has hypen in it.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-14 : 14:07:55

I think your data is not following a consistent format so unless you determine how all data can come you cant have single logic to handle them all!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

anjali5
Posting Yak Master

121 Posts

Posted - 2012-02-14 : 14:10:32

the query you gave me returned 3107 records.
I just need the query that has consistent length, I mean the right data otheriwse I want to return null.

Is it possible to do that so for 13867ER021747, I want the col2 to be null rather than parsing it correctly.

anjali5
Posting Yak Master

121 Posts

Posted - 2012-02-14 : 14:15:44

is it possible to ignore those 3107 records

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-14 : 14:29:25

[code]
UPDATE table
SET Col2 = STUFF(LEFT(Col1,CASE WHEN CHARINDEX('-',Col1)>0 THEN CHARINDEX('-',Col1) ELSE LEN(Col1)+1 END -1),1,PATINDEX('%[0-9]%',Col1)-1,'')
WHERE LEN(STUFF(LEFT(Col1,CASE WHEN CHARINDEX('-',Col1)>0 THEN CHARINDEX('-',Col1) ELSE LEN(Col1)+1 END -1),1,PATINDEX('%[0-9]%',Col1)-1,''))<=5
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

anjali5
Posting Yak Master

121 Posts

Posted - 2012-02-14 : 14:38:20

Ok, I talked to the DBA. I just need to look for those records that has hyphen in it and I just need 5 characters before hyphen, those characters can be alphanumeric.

so for this case
123ABC-34234

I need
23ABC

any help will be appreacited.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-14 : 14:56:04

then this is enough


UPDATE table
SET Col2 =RIGHT(LEFT(Col1,CASE WHEN CHARINDEX('-',Col1)>0 THEN CHARINDEX('-',Col1) ELSE LEN(Col1)+1 END -1),5)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

anjali5
Posting Yak Master

121 Posts

Posted - 2012-02-14 : 15:27:05

I just want NULL in col2 if there is no hyphen in col1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-14 : 15:30:10

You really want spoonfed results dont you?

I thought you could derive it for yourself!


UPDATE table
SET Col2 =CASE WHEN CHARINDEX('-',Col1)>0 THEN RIGHT(LEFT(Col1,CHARINDEX('-',Col1)-1),5) ELSE NULL END

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

anjali5
Posting Yak Master

121 Posts

Posted - 2012-02-14 : 15:49:46

Yes, I could have derived myself. I actually posted the question and then tried doing myself. I guess I wasn't confident enough that I can make it work.

Thanks again for all your help!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-14 : 16:02:34

No problem
you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources