SQL Query Problem?

Question

How can i split value from one column to 2 columns using SQL Query

e.g.

address
1234Delhi
125Mumbai
453Calcutta


address1 address2
123 Delhi
125 Mumbai
453 Calcutta

Answer 1

There are a couple ways.

If the column were a fixed-field concatenated string of three numeric characters + varying lenght alpha string, you would do this:

select
left(address, 3) as Address1 ,
substring(address, 4, len(address)) as Address2
from
table
GO


The other way is more sophisticated, as it requires developing a logical algorithm that can identify where in a string a pattern changes. Here is how I did it, in MS-T/SQL:

declare @pMyString varchar(30)
declare @pLi as integer

DECLARE MyCursor1 CURSOR FOR

SELECT Address
FROM MyTable


open MyCursor1

FETCH NEXT FROM MyCursor1
into @pMyString

while @@Fetch_Status = 0
begin
select @pLi = 1

while isnumeric(substring(@pMyString, @pLi, 1)) = 1

begin
select @pLi = @pLi + 1

continue

end

--at this point, you know where the numeric prefix and the alpha sufix seperate
print left(@pMyString, @pLi-1) + ' ' + substring(@pMyString, @pLi, len(@pMyString))

FETCH NEXT FROM MyCursor1

INTO @pMyString

end

Close MyCursor1

Deallocate MyCursor1
GO

Answer 2

If it's sure that the numbers are the left three characters just use the left function. If it's not sure just create a loop and truncate everytime one character from the right side of the string and ask isnumeric and then splitt it.

Example 1:

@myvalue_Numeric = Left(address, 3)
@myvalue_String = Right(address, Len(address) - 3)

Example 2
For i = 0 to Len(address)
If isnumeric(Left(address, Len(address) - i) then
@myvalue_Numeric = Left(address, Len(address) - i)
@myvalue_String = Right(address, Len(address) - i)
End if
Next i


Helmut

Answer 3

choose unique or use and AND fact or Use and if / else in some middle language to trim your consequences. My wager is you'll get what you want with choose unique. choose unique will in ordinary words pull one get admission to for "Steve Johnson" if he shows up more effective than once.. the worry you may run in to is that if there is actual more effective than one Steve Johnson, so ascertain your choose fact does the interest of tangible figuring out...

Answer 4

using only one query:

select Left(address, 3) as address1, Right(address, Len(address) - 3) as address2 from MyAddressTable

Answer 5

priyanka.batra This link might help you

http://scholar.google.com/scholar?hl=en&lr=&q=split+value+from+one+column+to+2+columns+using+SQL+Query&btnG=Search