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 |
|
ashishashish
Constraint Violating Yak Guru
408 Posts |
 Posted - 2009-04-19 : 12:09:46
|
| hi.. i want to split my data as,, likeif i have a varchar column like this...in which value are..abc-de-fghiso how i split that data like,,,,abc de fghi mean for checking purpose of data i want that data be split when '-' comes in it,,,so is there anyway to do soThanks,, |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
|
ashishashish
Constraint Violating Yak Guru
408 Posts |
Posted - 2009-04-20 : 00:41:17
|
| mean i have to found some method by which i can check my data ,,and first three characters must be ABCand some other checks...so there is any way to do so..mean any approach from which i get this thing. |
 |
|
|
bklr
Master Smack Fu Yak Hacker
1693 Posts |
Posted - 2009-04-20 : 01:38:53
|
| try this one declare @str varchar(32)select @str = 'abc-de-fghi'select left(@str,charindex('-',@str,1)-1),substring(@str,charindex('-',@str,1)+1,charindex('-',@str,charindex('-',@str,1)+1)- charindex('-',@str,1)-1),right(@str,charindex('-',reverse(@str),1)-1)select @str = replace(@str,'-','.')select parsename(@str,3),parsename(@str,2), parsename(@str,1) |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2009-04-20 : 03:12:52
|
quote:
Originally posted by ashishashish
mean i have to found some method by which i can check my data ,,and first three characters must be ABCand some other checks...so there is any way to do so..mean any approach from which i get this thing.
Not clear. Can you give use some more details?MadhivananFailing to plan is Planning to fail |
|
|
|
ashishashish
Constraint Violating Yak Guru
408 Posts |
Posted - 2009-04-20 : 05:43:40
|
| Hi i tell u wat i did till den,, well i did this but i want some simple method to this...if my data is like this..........'123-45-6789''123-45-67.89''ABC-12-3455''123-45-67890'So i want to validate this data and fetch the rows which are successfully validates ,,,some of validation terms are...1)That data always in this format xxx-xx-xxxx2)First digits always numeric,,,ABC and some other like this not allowed.3)middle two never be zero like 00.So i use that approach for this,,,,select * from @t where len(data)=11 and substring(data,4,1)='-' and substring(data,7,1)='-' and left(data,3)<>'000' and right(data,4)<>'0000' and substring(data,5,2) <>'00' and left(data,3) not in (select number from master..spt_values where type='P' and number between 734 and 749) and left(data,3) between '100' and '999'but i found this one little wrong somewhere so i just want to know that if there is any other method to validate that data or not??may be this time i will make u understand that what exactly i want..Thanks in Advance... |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2009-04-20 : 08:49:57
|
| Try thisselect * from @twhere data like '[0-9][0-9][0-9]-[1-9][1-9]-[1-9][1-9][1-9][1-9]'MadhivananFailing to plan is Planning to fail |
|
|
|
ashishashish
Constraint Violating Yak Guru
408 Posts |
Posted - 2009-04-20 : 13:17:42
|
| yea thanks may be i solve by problem by this let me check it and get back to u...iF theRe iS a wAy iN tHen theRe iS a wAy oUt.. |
|
|
|
ashishashish
Constraint Violating Yak Guru
408 Posts |
Posted - 2009-04-21 : 09:24:28
|
| Ohhh Many Many Thankss it works perfectly fine for meThanks Again Madhivanan Sir...iF theRe iS a wAy iN tHen theRe iS a wAy oUt.. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2009-04-21 : 09:44:10
|
quote:
Originally posted by ashishashish
Ohhh Many Many Thankss it works perfectly fine for meThanks Again Madhivanan Sir...iF theRe iS a wAy iN tHen theRe iS a wAy oUt..
Thanks and You are welcome  MadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
|
|
Split data