SQL Help

Independent houses near kukatpally | Apartments in Pragathi Nagar | AndhraVaani.com | Log Out | Topics | Search Register | Edit Profile

Author Message Netman Pilla Bewarse Username: Netman Post Number: 76 Registered: 03-2004 Posted From: 124.43.216.200 Posted on Friday, March 16, 2007 - 1:06 pm: Ok, this is the way I did, My values in address field are seperated by a comma followed by a space. I converted all the spaces with a 'non-breaking space'(Ascii code 160) REPLACE(address, " ",CHAR(160)) Now all the values in address field will be one string without any 'breaking space' which is the criteria used by 'word wrap' for wrapping a lengthy string into multiple lines. Now the 'comma with non-breaking space' has to be converted to 'comma with breaking space' so that the address can be wrapped only at the spaces, i.e after a value. REPLACE(formatted_address, "," + CHAR(160), ", ") So the complete SQL statement is: Select REPLACE(REPLACE(address, " ", CHAR(160)), "," + CHAR(160), ", ") from Table Just 2 nested REPLACE functions, combined with CHAR function, 'non-breaking space' and a concatenation operator(+). intakante easy process untademo naaku teliyadu. This worked great on about 10K records. Nayakudu Vooriki Bewarse Username: Nayakudu Post Number: 5793 Registered: 05-2005 Posted From: 129.188.69.129 Posted on Friday, March 16, 2007 - 12:38 pm: Tmbuff mama nenu kuda ade SQL iddam anukunna kani baddakam vesindi.. aa instr chala sarlu use cheyyali kada.. so medalakunda kurchunna Andhrawala Desanike Pedda Bewarse Username: Andhrawala Post Number: 9979 Registered: 03-2004 Posted From: 192.58.204.226 Posted on Friday, March 16, 2007 - 12:35 pm: Netman mama, I am in IT. Work with Microsoft Technologies, Oracle and Crystal reports Tmbuff Pilla Bewarse Username: Tmbuff Post Number: 160 Registered: 04-2004 Posted From: 159.153.4.51 Posted on Friday, March 16, 2007 - 12:17 pm: SELECT SUBSTR('XYZ,ABC,123,456', 1, INSTR('XYZ,ABC,123,456', ',',1,1)-1), SUBSTR('XYZ,ABC,123,456', INSTR('XYZ,ABC,123,456', ',',1,1)+1, (INSTR('XYZ,ABC,123,456', ',',1,2)-INSTR('XYZ,ABC,123,456', ',',1,1))-1) FROM DUAL you can use this kind of logic, if this works for you. This method works best if you have max value for predefined number of values in the string. Testuser Pilla Bewarse Username: Testuser Post Number: 6 Registered: 03-2007 Posted From: 143.166.226.42 Posted on Friday, March 16, 2007 - 12:05 pm: UDF is nothing but a UserDefined Function Much better way to write the UDF. select a1, udf(a2,maxcharactersperline) from table where a1 and a2 are columns in the table and a2 is the address field. Testuser Pilla Bewarse Username: Testuser Post Number: 5 Registered: 03-2007 Posted From: 143.166.226.42 Posted on Friday, March 16, 2007 - 12:04 pm: Netman mama try to write A UDF which takes in your Column that has the address info. In the function then try to format the information as you want by inserting new line characters as per your requirement . Make suer your UDF returns the updated string. Once the UDF is done then you can use that function as any SQL Built function in your SQL Statement. select a1, udf(a2) from table where a1 and a2 are columns in the table and a2 is the address field. Netman Pilla Bewarse Username: Netman Post Number: 75 Registered: 03-2004 Posted From: 203.81.100.26 Posted on Friday, March 16, 2007 - 12:58 am: >>Naaku antha time vunte mee mails ki regular gaa replies kotte vaadini kada? Bewarse lanta, inta busy ayipote, Bewarse sangam emaipoddi? inta busy avataniki Arjun effect aa? Netman Pilla Bewarse Username: Netman Post Number: 74 Registered: 03-2004 Posted From: 203.81.100.26 Posted on Friday, March 16, 2007 - 12:52 am: 420 Actual ga idi nenu telisi telavakunda half-knowledge tho, lucky ga 2 or 3 attempts lo execute chesanu, data naa daggara undi kabatti. Data lekunda, sql statement lo marpulu cherpulu chesukone avakasam lekunda, solution cheppadam kastame deeniki. daniki todu nenu exact ga problem explain chesi undaka povachchu. Live lo nenu ee problem face chesanu kabatti, plus idi chala mandiki edurayye problem kabatti post chesa, evaranna SQL ante kick unna members try chestaremo ani. Andhrawala cheppinattu REPLACE function, inko clue CHAR function SQL statement lo upayoginchali. Any takers? Gaali Bewarse ke Bewarse! Username: Gaali Post Number: 12033 Registered: 03-2004 Posted From: 69.237.226.149 Posted on Friday, March 16, 2007 - 12:45 am: >>ante ippati daaka chudaleda ee problem ni nuvvu? almost 12 hrs!!! Naaku antha time vunte mee mails ki regular gaa replies kotte vaadini kada? Prasanth Bewarse ke Bewarse! Username: Prasanth Post Number: 24470 Registered: 03-2004 Posted From: 203.200.218.2 Posted on Friday, March 16, 2007 - 12:27 am: mr. 420... neenga engai?bengaluru?? Cause of my illness:suppressing my anger myself 420 Bewarse Username: 420 Post Number: 1180 Registered: 12-2006 Posted From: 59.92.192.137 Posted on Friday, March 16, 2007 - 12:23 am: NETMAN bedar . I am in India right now .. and unable to help you .. however soultion for your faablem is erry simple .. I have done something similar long long ago in PL/SQL .. for a OWB project .. trying searching for PL/SQL STRING TOKENIZER in google .. there should be many functions around .. 420 Bewarse Username: 420 Post Number: 1179 Registered: 12-2006 Posted From: 59.92.192.137 Posted on Friday, March 16, 2007 - 12:20 am: Hello bedars .. India kwaling all u waste NRI's Netman Pilla Bewarse Username: Netman Post Number: 73 Registered: 03-2004 Posted From: 203.81.100.26 Posted on Thursday, March 15, 2007 - 11:45 pm: ante ippati daaka chudaleda ee problem ni nuvvu? almost 12 hrs!!! karnudini vodilesina kunti laaga tayarayyavu edO maa Gaali bidda idani, deenni pattukoni nee abhimanulu veladutunnaru Gaali Bewarse ke Bewarse! Username: Gaali Post Number: 12028 Registered: 03-2004 Posted From: 69.237.226.149 Posted on Thursday, March 15, 2007 - 11:29 pm: Mama Baagunnara? Yee roju disk space issues vachaayi AV server meda. Nenu yippudee choosi konchem clear chesi vaallaki mail kottanu. Yika pothe page views pettamani janaalu godava cheste yevarino pattukoni cheyinchaanu. Adi initial gaa probs create chesindi kaani yippudu anthaa baane vunnatlundi mari. Netman Pilla Bewarse Username: Netman Post Number: 72 Registered: 03-2004 Posted From: 203.81.100.26 Posted on Thursday, March 15, 2007 - 11:11 pm: Enti Gaali alludu postaka postaka chala kalam taravata edo avasarardam oka thread postite, nuvvu kooda maa Campu(tar) gurinchi matladevadivena ani, DB ne apesavu. ee madhya DB ni improve chesavani edo post lo chusanu, HTML badulu BTML emanna develop chesava, na computer DB ni access cheddamani try cheste, Internet Explorer 'nenu tattukolenu' ani close ayipotundi. 'sookshma manta mettanaina' vallu report pampiyyamantunnaru research chestaniki, bahusa nee kotta BTML nemo!! Netman Pilla Bewarse Username: Netman Post Number: 64 Registered: 03-2004 Posted From: 124.43.197.32 Posted on Thursday, March 15, 2007 - 12:02 pm: >>>ORA:xxxx - too many values ORA:yyyy - no data found hehehe idi poortiga ardam kaledu kaani, nenu tondaralo * vadanu, patient nunchi jabbulu kooda copy ayipotayi. * place lo sql ani matrame vadatademo. Nayakudu Vooriki Bewarse Username: Nayakudu Post Number: 5764 Registered: 05-2005 Posted From: 129.188.69.161 Posted on Thursday, March 15, 2007 - 11:54 am: >>select * into me from patient ORA:xxxx - too many values ORA:yyyy - no data found hehehe Netman Pilla Bewarse Username: Netman Post Number: 63 Registered: 03-2004 Posted From: 124.43.197.32 Posted on Thursday, March 15, 2007 - 11:49 am: >>Athani daggariki ekkuvaga SQL lo Job chese Patients vasthaaru kaabatti ante patient ki stethoscope petti, select * into me from patient ani statement execute chestada!! Bochum Desanike Pedda Bewarse Username: Bochum Post Number: 10597 Registered: 04-2004 Posted From: 140.105.47.82 Posted on Thursday, March 15, 2007 - 11:40 am: >>>>Andhrawala Doctor ani anukuntunnanu ee DB ni follow ayye daani batti. kaani atanu ela koddiga daggara ga answer cheppagaligadu Athani daggariki ekkuvaga SQL lo Job chese Patients vasthaaru kaabatti} Netman Pilla Bewarse Username: Netman Post Number: 62 Registered: 03-2004 Posted From: 124.43.197.32 Posted on Thursday, March 15, 2007 - 11:32 am: Crystal reports lo unde functions anni SQL lo kooda pani chestayi. meeku problem exact ga ardam ayyetattu cheppaleka povadam valla, meeru anta interest chupinchadam ledu kaani, koddiga daani meeda manasu pedite rakunda undadu. Andhrawala Doctor ani anukuntunnanu ee DB ni follow ayye daani batti. kaani atanu ela koddiga daggara ga answer cheppagaligadu Nayakudu Vooriki Bewarse Username: Nayakudu Post Number: 5760 Registered: 05-2005 Posted From: 136.182.158.129 Posted on Thursday, March 15, 2007 - 11:19 am: Then look out for the functions that comes with Crystal reports mama... this is something should be done using string manipulation functions available in the crystal reports rather than using SQL Netman Pilla Bewarse Username: Netman Post Number: 61 Registered: 03-2004 Posted From: 124.43.197.32 Posted on Thursday, March 15, 2007 - 11:13 am: Nayakudu There is an order starting from Doorno to District or state, but values of some may be there and some may not be there depending on the address. ASP,JSP anta scene ledu naku, edo crystal reports dwara invoicelu print cheyyali SQL server nunchi data teesukoni. Mamulaga a textbox ni address field ki link cheste, address printing egudu digudu ga vastundi. ala kakunda, oka paddati lo ravalani. Nayakudu Vooriki Bewarse Username: Nayakudu Post Number: 5753 Registered: 05-2005 Posted From: 136.182.158.129 Posted on Thursday, March 15, 2007 - 11:01 am: ASP kani JSP kani use chesthunnava user ki present cheyyadaniki data ni Nayakudu Vooriki Bewarse Username: Nayakudu Post Number: 5752 Registered: 05-2005 Posted From: 136.182.158.129 Posted on Thursday, March 15, 2007 - 11:00 am: Netman mama does address field always have the values in the order Doorno, Bldgname,Streetname, Area, Town, district Netman Pilla Bewarse Username: Netman Post Number: 60 Registered: 03-2004 Posted From: 124.43.197.32 Posted on Thursday, March 15, 2007 - 10:46 am: PD Your code is too complicated(atleast for a layman like me). Andhrawala You have come close! To an extent this is OK, but I believe the commas are replaced by newline character and every value will come in a seperate line, like: 241 X Complex MG Road Governorpeta Vijayawada 02 But what if I want '241' and 'X Complex' to be in one line if there is enough space in the line; 'MG Road' and 'Governorpet' to be in one line if both can be accomodated. Because if any address is having too many short length 'values', it might go beyond the pre-printed address box as every 'value' is printed on a seperate line:-) Also a space is followed by the comma in my records. The pre-printed address box can accomodate say a maximum of 30 to 40 characters per line and say about 4 to 5 lines. Andhrawala Desanike Pedda Bewarse Username: Andhrawala Post Number: 9976 Registered: 03-2004 Posted From: 192.58.204.226 Posted on Thursday, March 15, 2007 - 8:42 am: replace( string1, string_to_replace, [ replacement_string ] ) Select replace(address,'\n',',') from Table This shall replace the newline character with , Ravanabrahma Bewarse Username: Ravanabrahma Post Number: 1960 Registered: 06-2004 Posted From: 57.68.10.185 Posted on Thursday, March 15, 2007 - 7:53 am: Codes Desanike Pedda Bewarse Username: Codes Post Number: 7949 Registered: 05-2006 Posted From: 80.80.13.18 Posted on Thursday, March 15, 2007 - 7:53 am: SQL loo kavali anadu kadha.. eey database ? None is worse than other.. and Everyone is better than the other.. Gochi Bewarse ke Bewarse! Username: Gochi Post Number: 37405 Registered: 07-2004 Posted From: 24.2.196.220 Posted on Thursday, March 15, 2007 - 7:50 am: hemu maayya Gochi Bewarse ke Bewarse! Username: Gochi Post Number: 37404 Registered: 07-2004 Posted From: 24.2.196.220 Posted on Thursday, March 15, 2007 - 7:50 am: PDioo Ravanabrahma Bewarse Username: Ravanabrahma Post Number: 1959 Registered: 06-2004 Posted From: 57.68.10.185 Posted on Thursday, March 15, 2007 - 7:50 am: coding tho annava. kiki. swarry. Ravanabrahma Bewarse Username: Ravanabrahma Post Number: 1958 Registered: 06-2004 Posted From: 57.68.10.185 Posted on Thursday, March 15, 2007 - 7:48 am: save chesetappudu tab delimited ga save cheyyochemo chodu mama xls laaga kakunda. Proofdada Bewarse ke Bewarse! Username: Proofdada Post Number: 52581 Registered: 03-2004 Posted From: 80.121.144.9 Posted on Thursday, March 15, 2007 - 7:48 am: mama neenu programming oka paddahti loo cheyyanu...tough ayina easy ayinaa aa time lo thochina vidamgaa velli pothaa..yeppudu prob pl/sql or data retrieving foramtting daggaree vashtundi programmer ku prob.. any how nuvvu cheppindhi prob ayithee count number ,s and count the number charectors between ,s what will be the max number fo commas you can find for a full detailied record akkadi nunchi , less vasthee ...yekkadanna rend commas madya loo count charectors 0 vasthee aaa field missing anukoo.. inkaa detailed gaa retrieving into arrays antee.. max number of record mundhu find out cheesi or while end of records option thooti.. tooki gaa oka simple logic.. for(i = 0; i< iMaxrecords; i++) { rc1 = ds1->Tables["ZHMONTHLY"]->Rows[i]; ob1 = rc1->default["PHISTORY_PERFORMANCE"];// retrieving field aArrayString->SetValue(ob1,i);// adding into aray ixy1 = ixy1 + 1; } ob1 is object, str is String aArrayString as array<String ^>^ aArrayString; and zhmonthly is table name phistory_performance is field name malli for loop use cheesi str = aArrayString->getvalue(i); FinalString = str + FinalString; alaa cheesi motham record retrieve cheyyu.. idi chaana long way anukunte lite thesuko mama nannnu aaapadddu.... Gochi Bewarse ke Bewarse! Username: Gochi Post Number: 37401 Registered: 07-2004 Posted From: 24.2.196.220 Posted on Thursday, March 15, 2007 - 7:44 am: deeniki oka pedda procedure raayalemo netman maama.....simple aithe kaadhulaa undi Netman Pilla Bewarse Username: Netman Post Number: 59 Registered: 03-2004 Posted From: 124.43.197.32 Posted on Thursday, March 15, 2007 - 7:36 am: address lannee oke laaga undavu. konnintlo doorno undochchu konnintlo undaka povachchu. doorno lekapote akkada comma's madhya kaaLee(Null string) undadu, address direct ga Bldgname tho gaani, Streetname tho gaani start ayipovachchu. alage migata 'values' ki kooda. array dwara veelaite aa syntax kavali. Proofdada Bewarse ke Bewarse! Username: Proofdada Post Number: 52580 Registered: 03-2004 Posted From: 80.121.144.9 Posted on Thursday, March 15, 2007 - 7:27 am: rec motham arrayString looki retrieve cheesi ..mass gaa strings add sesukunte oka line lo vachesuddi gaa.. nannnu aaapadddu.... Bochum Desanike Pedda Bewarse Username: Bochum Post Number: 10551 Registered: 04-2004 Posted From: 140.105.47.82 Posted on Thursday, March 15, 2007 - 7:20 am: Mana Nayakudu unte ventane cheppevadu answer....em ayyado..... aina mana Naatu mama unnadu gaa.... Netman Pilla Bewarse Username: Netman Post Number: 58 Registered: 03-2004 Posted From: 124.43.197.32 Posted on Thursday, March 15, 2007 - 7:16 am: 'Customers Table' lo 'address field' lo Doorno, Bldgname,Streetname, Area, Town, district values(as applicable) comma tho seperate chesi unnayi, like rec1: 240/1, X complex, MG Road, Governorpet, Vijayawada 2. rec2: 1B, Beside Vijaya Talkies, Eluru road, Suryarao peta, Vijayawada 02, Krishna Dt, Andhra Pradesh rec3: Lakshmi Nilayam, Poornachandra Rao veedhi, Patamata Lanka, Vijayawada-6 ...ila values anni, oka record lo, oke string lo, comma tho seperate ayyi unnAyi ee field ni oka address box lo print cheyyAli. address box length,width adjust chesukovachchu, 'word wrap' cheyyochchu, oka line lo enni values anna print cheyyochchu. kanee oka 'value' rendu lines loki split avvakoodadu 'word wrap' valla. For ex: 241, X Complex, MG Road, Governorpeta, Vijayawada 02.... ani print avvochchu gaani 241, X Complex, MG Road, Governorpet Vijayawada 2..... ani print avakoodadu. Table nunchi address field retrieve chesetappudu, SQL upayoginchi, ee field lo oke value, multiple lines loki split avakunda format cheyyagalara? like... Select format??(address) from Table 'value' ante 'doorno' gaani, 'streetname' gaani, 'area name' gaani etc.