SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 charindex
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

peace
Constraint Violating Yak Guru

414 Posts

Posted - 05/16/2013 :  03:26:29  Show Profile  Reply with Quote
how can i only get from the column '12345'?
but some of the data has smaller room number.

ROOMCOLUMN
the majestic room 12345
the majestic room 123
the majestic room 1
the majestic room 1459

result should be as below:
ROOMCOLUMN
12345
123
1
1459

waterduck
Aged Yak Warrior

Malaysia
965 Posts

Posted - 05/16/2013 :  03:45:05  Show Profile  Reply with Quote
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
Go to Top of Page

peace
Constraint Violating Yak Guru

414 Posts

Posted - 05/16/2013 :  03:48:11  Show Profile  Reply with Quote
i will still get the rest of the data..
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 05/16/2013 :  04:02:52  Show Profile  Reply with Quote

SELECT STUFF(ROOMCOLUMN, 1, PATINDEX('%[0-9]%',ROOMCOLUMN)-1, '')
FROM @foo


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

peace
Constraint Violating Yak Guru

414 Posts

Posted - 05/16/2013 :  04:19:29  Show Profile  Reply with Quote
not possible to use charindex or substring?
because some of the data has like this:


the majestic room 12345 reference 10
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 05/16/2013 :  04:32:10  Show Profile  Reply with Quote
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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

peace
Constraint Violating Yak Guru

414 Posts

Posted - 05/16/2013 :  04:45:52  Show Profile  Reply with Quote
this is the data.
I only want the room number.

ROOMCOLUMN
the majestic room 12345
the majestic room 123
the majestic room 1
the majestic room 1459
the majestic room 123444 reference 10 block
the majestic room 4535 reference 15 block
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 05/16/2013 :  05:08:09  Show Profile  Reply with Quote
assuming no other formats being present, you can use below

SELECT LEFT(STUFF(ROOMCOLUMN, 1, PATINDEX('%[0-9]%',ROOMCOLUMN)-1, ''),CHARINDEX(' ',STUFF(ROOMCOLUMN, 1, PATINDEX('%[0-9]%',ROOMCOLUMN)-1, '')+ ' ')-1)
FROM @foo



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

peace
Constraint Violating Yak Guru

414 Posts

Posted - 05/16/2013 :  05:14:50  Show Profile  Reply with Quote
got it.
is working fine.
thanks!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 05/16/2013 :  05:18:54  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000