Excluding word after dot "." in SQL

Question

Im using ms sql compact 4.0 for db and I just wanna ask How can I exclude word after dot "." to add on my context-hint .

like for Example in my db: http://oi41.tinypic.com/2f06053.jpg

for TypeName "Indicators" , e.g in Indicators.RelativeStrengthIndex, i only want the word "Indicators" to remain and delete word ".RelativeStrengthIndex" or after a dot . same as others .

DataExplorer://ObjDB.sdf/Table/[tb_CsType]

to be appear in my context-hint:

http://oi41.tinypic.com/2j5xczm.jpg

code for my autocomplete:

string conStr = @"Data Source=C:\Users\z\Desktop\Prot\DB\ObjDB.sdf";

                SqlCeDataReader sqldrAllData;
                SqlCeConnection sqlCon = new SqlCeConnection(conStr);

                ///1
                SqlCeCommand sqlCmd = new SqlCeCommand("Select TypeName, Syntax, Description From tb_CsType", sqlCon);

                ///2
                //SqlCeCommand sqlCmd2 = new SqlCeCommand("Select TypeName, Syntax, Description From tb_CsMember", sqlCon);

                sqlCon.Open();
                sqldrAllData = sqlCmd.ExecuteReader();

                List<string> lstTypeName = new List<string>();
                while (sqldrAllData.Read())
                {
                    lstTypeName.Add(sqldrAllData["TypeName"].ToString());
                }

                foreach (var word in lstTypeName)
                    yield return (new Autoc(word) { ImageIndex = 3 });


                sqlCon.Close();

then question was how can i exlude word after "." in my sql . thanks in advance! more power!

score 1 · Answer 1 · edited May 23 '17 at 12:20

1

Try this in your sql query

Select TypeName, Syntax,LEFT(Description, CHARINDEX('.', Description) - 1)  From tb_CsType

I think its just like this question SQL Server replace, remove all after certain character

For the Sql Server Compact edition there is no Left function. Try this in compact editon.

SELECT TypeName, Syntax, SUBSTRING(Description, 0, CHARINDEX('.', Description)) AS Desc FROM tb_CsType

This will remove the rest of the string value after '.'.In the SUBSTRING function the start value 0 means from the start of the word

edited May 23 '17 at 12:20

Community

1
1

answered Sep 12 '13 at 06:22

udaya726

1,010
6
21
41

sql executive error: executed sql statement: SELECT TypeName, Syntax, LEFT(Description, CHARINDEX('.', Description) -1) AS Expr1 FROM tb_CsType Error Source: SQL Server Compact ADO.NET Data Provider Error Message: There was an error parsing the query.[ Token line number = 1, Token line offset = 26, Token in error = LEFT ] – Elegiac Sep 12 '13 at 06:31
Yeap in the compact edition there is no Left function.Change the query as follows SELECT TypeName, Syntax, SUBSTRING(Emp_name, 0, CHARINDEX('.', Description)) AS Desc FROM tb_CsType This will remove the rest of the string value after '.'.In the SUBSTRING function the start value 0 means from the start of the word – udaya726 Sep 12 '13 at 07:21
what should i do now? @udaya – Elegiac Sep 12 '13 at 07:24
it says "Unableto parse querytext" and same error appears as before – Elegiac Sep 12 '13 at 07:25

Elias Platek · Answer 2 · 2013-09-12T13:43:49.017

0

You can use substring(string, pos, length)

SELECT TypeName, Syntax,
SUBSTRING(Description, 
  CHARINDEX('.', Description, (CHARINDEX('.', Description) + 1)) + 1, 
  LEN(Description) - CHARINDEX('.',Description) + 1)
From tb_CsType;

or you can remove the string in .NET before adding to the list:

List<string> lstTypeName = new List<string>();
while (sqldrAllData.Read())
{
    var s = sqldrAllData["TypeName"].ToString();
    lstTypeName.Add(s.IndexOf('.') > 0 ? s.Substring(0, '.') : s);
}

edited Sep 12 '13 at 13:43

answered Sep 12 '13 at 06:53

Elias Platek

1,074
1
9
16

An unhandled exception of type 'System.ArgumentOutOfRangeException' occurred in mscorlib.dll Additional information: Index and length must refer to a location within the string – Elegiac Sep 12 '13 at 07:03
pointing to lstTypeName.Add(s.Contains('.') ? = s.Substring(0, '.') : s); – Elegiac Sep 12 '13 at 07:03
Ok it's edited. I didn't try to compile the code, but you get the idea ;) – Elias Platek Sep 12 '13 at 13:44

Software Engineer · Answer 3 · 2013-09-12T07:31:47.287

0

The function LEFT is not supported in Sql Server CE, see list of supported functions but you can use the combination of SUBSTRING and CHARINDEX in your case:

string typedWord = "YourTypedWord" + "%";
string sql = string.Format("select TypeName, Syntax, SUBSTRING(Description, 0, CHARINDEX('.', Description)) as Description from tb_CsType WHERE Description LIKE '{0}'", typedWord);

edited Sep 12 '13 at 07:31

answered Sep 12 '13 at 07:04

Software Engineer

3,906
1
26
35

its select all that has '.' but not update . the time i type Indicators its still there :/ @Coder – Elegiac Sep 12 '13 at 07:07
i dont want to change in the db . i still want it to be there (the .qweqwewe) but in the context-hint it will gone – Elegiac Sep 12 '13 at 07:09
You obviously need to use WHERE clause with LIKE in sql to get only those results that match with your _typed_ indicator. – Software Engineer Sep 12 '13 at 07:32

Excluding word after dot "." in SQL

3 Answers3