select currentyearcooperativecase CHARINDEX3939 currentyearcooperative when 0 then currentyearcooperative else SUBSTRINGcurrentyearcooperative 1 CHARINDEX3939 currentyearcooperative 1 end FarmerCoNocase CHARINDEX3939 currentyearcooperative when 0 then currentyearcooperative else SUBSTRINGcurrentyearcooperative CHARINDEX3939 currentyearcooperative 1 LENcurrentyearcooperative end FarmerCoNamefrom tblfarmerregistrationivorycoast where farmer

Display first 10 characters in after splitting in SQL Server

narasima...

SOLVED CLOSED ACTIVE ANSWERED
User: narasiman
Posted: on Aug 21, 2018 12:58 AM
Forum: SQL Server
Replies: 2
Views: 2727

select currentyearcooperative,case CHARINDEX('-', currentyearcooperative) when 0 then currentyearcooperative else SUBSTRING(currentyearcooperative, 1, CHARINDEX('-', currentyearcooperative) - 1) end FarmerCoNo,
case CHARINDEX('-', currentyearcooperative) when 0 then currentyearcooperative else SUBSTRING(currentyearcooperative, CHARINDEX('-', currentyearcooperative) + 1, LEN(currentyearcooperative)) end FarmerCoName
from tbl_farmerregistration_ivorycoast where farmersapcode = '1147215'

When i run the above i get output as follows

currentyearcooperative FarmerCoNO FarmerCoName

FANYOUDALA(DIAWALA) FANYOUDALA (DIAWALA) FANYOUDALA (DIAWALA)

From the above i want output as follows

currentyearcooperative FarmerCoNO FarmerCoName

FANYOUDALA(DIAWALA) FANYOUDALA FANYOUDALA

i want to display the first 10 characters of FarmerCoNo and FarmerCoName.

from the above sql query what changes i have to made to get my above output.

Andrea

0 1

Reply
User: Andrea
Replied: on Aug 21, 2018 12:58 AM
Report

Hi @narasiman, Please try the following Display data of two columns in one column one after other in SQL Query It might help you. Cheers Andrea.

dharmend...

0 2

Reply
User: dharmendr
Replied: on Aug 21, 2018 01:31 AM
Report

Hi narasiman,

Use SUBSTRING function which accepts string, start_pos, number_of_chars as parameter.

string - The string to extract from.
start_pos - The position to start extraction from. The first position in string is 1.
number_of_chars - The number of characters to extract.

DECLARE @tbl_farmerregistration_ivorycoast AS TABLE(currentyearcooperative VARCHAR(50))
INSERT INTO @tbl_farmerregistration_ivorycoast VALUES('FANYOUDALA(DIAWALA)')

select currentyearcooperative,case CHARINDEX('-', currentyearcooperative) when 0 then SUBSTRING(currentyearcooperative,1,10) else SUBSTRING(currentyearcooperative, 1, CHARINDEX('-', currentyearcooperative) - 1) end FarmerCoNo,
case CHARINDEX('-', currentyearcooperative) when 0 then SUBSTRING(currentyearcooperative,1,10) else SUBSTRING(currentyearcooperative, CHARINDEX('-', currentyearcooperative) + 1, LEN(currentyearcooperative)) end FarmerCoName
from @tbl_farmerregistration_ivorycoast

Output

currentyearcooperative	FarmerCoNo	FarmerCoName
FANYOUDALA(DIAWALA)	FANYOUDALA	FANYOUDALA

Display first 10 characters in after splitting in SQL Server

Company

Explore

Follow us

Contact Us