Author |
Topic |
peace
Constraint Violating Yak Guru
420 Posts |
Posted - 2013-05-16 : 03:26:29
|
how can i only get from the column '12345'?but some of the data has smaller room number.ROOMCOLUMNthe majestic room 12345the majestic room 123the majestic room 1the majestic room 1459result should be as below:ROOMCOLUMN1234512311459 |
|
waterduck
Aged Yak Warrior
982 Posts |
Posted - 2013-05-16 : 03:45:05
|
DECLARE @foo TABLE(ROOMCOLUMN VARCHAR(50))INSERT INTO @foo VALUES('the majestic room 12345'),('the majestic room 123'),('the majestic room 1'),('the majestic room 1459')SELECT *, STUFF(ROOMCOLUMN, 1, 18, '')FROM @foo |
|
|
peace
Constraint Violating Yak Guru
420 Posts |
Posted - 2013-05-16 : 03:48:11
|
i will still get the rest of the data.. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-05-16 : 04:02:52
|
[code]SELECT STUFF(ROOMCOLUMN, 1, PATINDEX('%[0-9]%',ROOMCOLUMN)-1, '')FROM @foo[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
peace
Constraint Violating Yak Guru
420 Posts |
Posted - 2013-05-16 : 04:19:29
|
not possible to use charindex or substring?because some of the data has like this:the majestic room 12345 reference 10 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-05-16 : 04:32:10
|
quote: Originally posted by peace not possible to use charindex or substring?because some of the data has like this:the majestic room 12345 reference 10
First tell us what all formats data can come. Then we can think of a generic approach to get you the required data------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
peace
Constraint Violating Yak Guru
420 Posts |
Posted - 2013-05-16 : 04:45:52
|
this is the data.I only want the room number.ROOMCOLUMNthe majestic room 12345the majestic room 123the majestic room 1the majestic room 1459the majestic room 123444 reference 10 blockthe majestic room 4535 reference 15 block |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-05-16 : 05:08:09
|
assuming no other formats being present, you can use belowSELECT LEFT(STUFF(ROOMCOLUMN, 1, PATINDEX('%[0-9]%',ROOMCOLUMN)-1, ''),CHARINDEX(' ',STUFF(ROOMCOLUMN, 1, PATINDEX('%[0-9]%',ROOMCOLUMN)-1, '')+ ' ')-1)FROM @foo ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
peace
Constraint Violating Yak Guru
420 Posts |
Posted - 2013-05-16 : 05:14:50
|
got it.is working fine.thanks! |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-05-16 : 05:18:54
|
welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|