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.
| Author |
Topic |
|
apantig
Posting Yak Master
104 Posts |
 Posted - 2005-08-18 : 22:32:06
|
Hi guys,How can I separate the data that is alpha numeric in type?FloorNumber8A8B8C11A11B12131415A I need to separate the 8 from A in 2 columns just like ....UnitLevel UnitCode8 A8 B And if the floor numbers do not have "letters" then let it be.Thanks. |
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2005-08-18 : 23:24:38
|
quote:
Originally posted by apantig
And if the floor numbers do not have "letters" then let it be.
Assuming that means FloorNumber maps into UnitLevel in this case ...There's a PATINDEX function you should look up in Books Online. quote:
PATINDEXReturns the starting position of the first occurrence of a pattern in a specified expression, or zeros if the pattern is not found, on all valid text and character data types.SyntaxPATINDEX ( '%pattern%' , expression )
So, PATINDEX('%[^0-9]%', FloorNumber+'z') -- Note the sentinal 'z' added to ensure a non-zero return value for PATINDEXWill find the first non-numeric character... so...SELECT LEFT(FloorNumber, PATINDEX('%[^0-9]%', FloorNumber+'a') - 1) As UnitLevel FROM MyTableShould return the numeric UnitLevel. I haven't tested this, but you should get the idea.The UnitCode is similar, but will require a CASESELECT CASE WHEN PATINDEX('%[^0-9]%', FloorNumber) = 0 THEN '' ELSE RIGHT(FloorNumber, LEN(FloorNumber) - PATINDEX('%[^0-9]%', FloorNumber) + 1) As UnitCode |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
|
|
|
|
|
Separate data