[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Query Syntax

52K

Solutions

20K

Contributors

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.

Share tech news, updates, or what's on your mind.

Sign up to Post

I need regular expression for following in Sql Server ( I hope it is same in vb.net or c#)
I am looking for a pattern  someletters somenumbers  some letters- someletters somenumbers  some letters     ( All capital letters)
  examples  AA111WW-B123SS
                      AB1DFG-S3ERT

   Another pattern I am looing is   somenumbers someletters somenumbers -someletters
     examples   11AAA123-SDFG
                          1AB23-AB
1
Exploring ASP.NET Core: Fundamentals
LVL 12
Exploring ASP.NET Core: Fundamentals

Learn to build web apps and services, IoT apps, and mobile backends by covering the fundamentals of ASP.NET Core and  exploring the core foundations for app libraries.

We have a SQL Server database with assets (financial products and linked objects, like loans, mortgages, real estate). An asset can be related to one or more customers. A customer can be related to one or more assets. See this table for an example.

Source data
I need to group all customers that share an asset - even when it's an indirect relation. For example, customer a shares assets with customers b and c. But c also shares an asset with d. This links d not only to c, but also to a and b. The same goes for e, f and g. Customers f and g share no asset, but they both share an asset with e - that makes them part of the same group.

Customer groups
I'm looking for a way to find all customer groups in the database. For your information: there will be about 2 million customers and 3 million assets. A customer group is expected to have 1 to 20 members. The result in this example would be something like this:

Query result
Can anyone help me to find a solution, keeping in mind the data volume? Your assistance is of course much appreciated!

This code will create and populate a table with the data from the example above.

create table dbo.AssetCustomer
( assetId int
, customerId char(1))

Insert into dbo.AssetCustomer values (1000, 'a')
Insert into dbo.AssetCustomer values (1000, 'b')
Insert into dbo.AssetCustomer values (2000, 

Open in new window

1
/*14.      Create a table variable using data in the dbo.HospitalStaff table with the following 4 columns
a.      Name – Located in the NameJob Column : Everything before the _
b.      Job – Located in the NameJob Column : Everything after the _
c.      HireDate
d.      City – Located in the Location Column : Everything before the –
e.      State – Located in the Location Column : Everyting after the –
*/

DECLARE @New_Hospitalstaff TABLE(
                            NAME Varchar (50),
                                          JOB   varchar (50),
                                          HIREDATE    Datetime,
                                          CITY  varchar (50),
                                        [STATE] varchar (50)
)
INSERT INTO    @New_Hospitalstaff

       
        SELECT  left(location, CHARINDEX('-', Location) -1)as staff_city
               right(location , len(Location)-CHARINDEX('-',Location)) as staff_state

       
                    
 FROM DBO.HospitalStaff

SELECT *
FROM @NEW_HOSPITALSTAFF



what did I get wrong in my syntas
0
We're trying to build a datagrid connected to an SQL database in UWP as a proof of concept. We're running into an issue declaring the DataTemplate that it doesn't understand the DataType, and I can't get it to process it properly. This has to be defined as we're binding the textboxes to the SQL database. We were using this site for reference https://docs.microsoft.com/en-us/windows/uwp/data-access/sql-server-databases    (we've made changes to suite our needs)

Code segment throwing errors
<DataTemplate x:DataType="local:Product">

The.xaml page with the errors (line 48)
<Page
    x:Class="App3.Views.TicketsPage"
    xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
    xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
    xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
    Style="{StaticResource PageStyle}"
    mc:Ignorable="d">

    <Grid x:Name="ContentArea">
        <Pivot
            x:Name="Items">
            <PivotItem Header="Open">
                <Grid Background="{ThemeResource SystemControlPageBackgroundChromeLowBrush}">
                    <RelativePanel>
                        <ListView Name="WorklogList"
                            SelectionMode="Single"
                            ScrollViewer.VerticalScrollBarVisibility="Auto"
                            ScrollViewer.IsVerticalRailEnabled="True"
                            

Open in new window

0
I have a sqlcmd command which I would like to not execute manually in the cmd window or in a Windows batch file, but to run as a SQL Server job, but putting the sqlcmd command line in the job results in an error on every single part of the command line, although the SQL Server doc gives an example of a sqlcmd call as a job step. So I'm a bit lost.

My SQL Server agent job looks like this: 1 step with the following content:

"C:\Program Files\Microsoft SQL Server\110\Tools\Binn\sqlcmd.exe" -S MyServerName -i "C:\temp\My T-SQL.sql" -v DWH = MyDBName -o "C:\temp\My T-SQL Log.txt"

When parsing this, the job window already complains about the -S bit. If I eliminate that, it complains about the -i bit, so clearly, something is completely wrong with the whole thing, but what ? Must be simple, but I can't figure it out. And, needless to say, the job runs but fails with the laconic error message "incorrect syntax near" and mentions the sqlcmd.exe part of the call.

Can someone help me ?

Thanks
Bernard
0
I have xml in which the data is present in a non-regular structure, and I can't use the value method to retrieve it.
By non-regular, I want the value of certain attributes and I can't predict how many nodes deep it might be.

The interesting part is that I want to retrieve attributes from the 3rd level of nodes (1st  level of 'Item') in one column, then place all other instances of a particular attribute, regardless of how many nodes deep it is, into the next column.  The first level always has the same structure (/ItemList/Definition/Item).
While the example shows a depth of perhaps 8 nodes, in reality there may be five or ten times that depth/complexity.

Can this be done?
Thanks,
Ed

DECLARE @TestXMLTable Table(XmlData xml) 
INSERT INTO @TestXMLTable 
SELECT
 ' <ItemList>
    <Definition Trigger="False">
      <Item Id="1" Status="True">
        <ItemDefinition ID="Name1-FirstLevelVariableText" Type="Custom" Key="0">
          <Items>
            <Item ID="Yada-VariableTextFor101" FieldID="101" ManyOthers="xxxxxx" >
              <ItemDefinition ID="yyyy" Expression="zzzzzz">
                <Items>
                  <Item ID="Yada-VariableTextFor102" FieldID="102" IgnoreNull="false" CompareValue="568|569|570">
                    <ItemDefinition ID="">
                      <Items />
                    </ItemDefinition>
                  </Item>
                  <Item ID="Yada-VariableText102b" FieldID="102" IgnoreNull="false" 

Open in new window

0
I have an example column data on an SQL Table which I wish to split into 4 different columns on an output table.  Essentially 4 output text  column will be created in the output table where contents will be determined between each special character value e.g.%.

Ideally can it be done in a loop where the control can be applied to a maximum of 4 outputs where the last column gets populated a remainedr?

Column Data example
Jane%Morris%Gweder Road% London%W29DD

Output Example:
Col 1, Cl2 ,Col3, Col4
Jane,Morris,Gweder Road, London,W29DD
0
Hi
I have Oracle SQL Dev version v17.4 with Java Platform 1.8.

I would like to connect to an Ms Access table but i can't see the tab. How can I add it?
Oracle Missing Ms Access tab
In an older Oracle SQL Dev, (1.5), i do have that tab.
0
MS SQL corruption error

Getting this error on a sbs 2011 server

The description for Event ID 8646 from source MSSQL$MICROSOFT##SSEE cannot be found. Either the component that raises this event is not installed on your local computer or the installation is corrupted. You can install or repair the component on the local computer.

If the event originated on another computer, the display information had to be saved with the event.

The following information was included with the event:

1
692197516
SUSDB

The specified resource type cannot be found in the image file

Any ideas?
sqlerr.PNG
0
Hi everyone,

I am learning and I want to make search with more filters. I have one table where I have three columns with foreign keys which are related to other tables. So, I have 4 tables. First table containt user details, second table contain category, third table contain institution, and fourth table containt city.

First table look like on picture below named (users)



Second table (named categories)



Third table (named institutions)



Fourth table (named city)



I have this HTML form:
<form action="izvjesce.php" method="GET">
										<div class="form-group col-lg-6">
										<select  class="form-control" name="status" required>
											<option  value="Aktivan" checked><b>Aktivan</b></option>
											<option  value="Neaktivan"><b>Neaktivan</b></option>
										</select>
										</div>
										<div class="form-group col-lg-6">
										<select  class="form-control" name="spol">
											<option  value=""><b>Spol</b></option>
											<option  value="Muški"><b>Muški</b></option>
											<option  value="Ženski"><b>Ženski</b></option>
										</select>
										</div>
										<div class="form-group col-lg-6">
										<select  class="form-control" name="pos_mj" >
											<option  value="">Izaberi mjesec</option>
											<option  value="siječanj">Januar/Siječanj</option>
											<option  

Open in new window

1
Fundamentals of JavaScript
LVL 12
Fundamentals of JavaScript

Learn the fundamentals of the popular programming language JavaScript so that you can explore the realm of web development.

hi i got the following record

 

abc*** i what it to be update to abc*###

 

or if i have abc** to be  abc##

 
am in oracle database 11gr2
this is my query
update noc_t_mrc set enc_encoded_reply=substr(enc_encoded_reply,1,length(enc_encoded_reply)-1)||'#' where enc_encoded_reply like '%*';

Open in new window

0
I am creating an after insert trigger that is supposed to update an existing row in the same table after a new insert.
Scenario:  This table records how long agents were on break.  They can go from break "Outbound" directly to break "Out to Lunch" without the "Outbound" break ever getting a `PauseEnd` timestamp.  This trigger is intended to put the NEW 'PauseStart'  into the existing 'PauseEnd' column of the agent's previous break.  I get no errors, but it doesn't do anything and I can't figure out why.

Here's the table and the trigger:

CREATE TABLE `agent_break_rec` (
  `id` int(10) NOT NULL,
  `AgentID` int(11) DEFAULT NULL,
  `AgentName` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `PauseCode` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `PauseStart` datetime DEFAULT NULL,
  `PauseEnd` datetime DEFAULT NULL,
  `PauseSec` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ixAgetCodeDT` (`AgentID`,`PauseCode`,`PauseStart`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=7144 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE DEFINER=`sysop`@`%` TRIGGER `tgEndBreakAfterInsert` AFTER INSERT ON `agent_break_rec` 
FOR EACH ROW update agent_break_rec set 
     PauseEnd=NEW.PauseStart 
where id=(select id from agent_break_rec where PauseEnd is null and AgentID=NEW.AgentID order by PauseStart desc limit 1);

Open in new window

0
I'm looking to see if there is a way to connect Access 2016 to SQL Express database using an OLE DB connection.
What I need to do is
 - import data into the SQL Express tables
 - create a form to update the SQL data
 - create queries with calculated fields
 - write reports

As of now I have the database created on my local C drive (C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\DATA\mydb.mdf) but in the future it will reside on the network with multiple users who will have access to the database and I don't want to have to create and ODBC connection.
0
Just need to append new rows from one SQL Table to another.   In the example below, I just need to append Table A to Table B.

SQL Table A

FOLID
---------
1234
3333

SQL Table B
FOLID   AttributeID  Name  
--------     --------------    -------
99998   33333333     Test

Expected Results:

SQL Table B
FOLID   AttributeID  Name  
--------     --------------    -------
99998   33333333     Test
1234
3333
0
We have a database related program (Event Wizard - https://www.eventwizardpro.com/) that was just woke up to these error messages.

This is on the login screen:
Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in C:\Program Files\EventWizard\Apache2\ewiz\includes\sessions.php on line 71

And after a successful login (allows login, doesn't give a invalid user name/password error) it gives this error:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in C:\Program Files\EventWizard\Apache2\ewiz\includes\sessions.php on line 71

Warning: Cannot modify header information - headers already sent by (output started at C:\Program Files\EventWizard\Apache2\ewiz\includes\sessions.php:71) in C:\Program Files\EventWizard\Apache2\ewiz\includes\sessions.php on line 74

I am sure these errors are program specific, but from the error messages can you recommend anything to do as I am not well versed in SQL or Apache or MySQL.
0
I am trying to create a sql query for a report that I will eventually create. In our erp software we have a manage shipping transactions screen where you put in a packingslip number and it returns details on the records. For example:

PACKING SLIP              Sales Order
PS-000000002511        SO-0001165
PS-000000002512        SO-0001165
PS-000000002500        SO-0001165

I can then double click on a record and it returns the detail. So in my erp software if I select  PS-000000002511, it will return 90 records of detail.

I have the following query based off of the two tables that house the packing slip.

SELECT SHIPTRNISSUE.PS_ID, SHIPTRNISSUE.SO_ISSUE_KEY, SHIPTRANS.SO_ID, SHIPTRANS.CUST_ID
FROM DELTEK.SHIP_TRN_ISSUE AS SHIPTRNISSUE INNER JOIN DELTEK.SHIP_TRN AS SHIPTRANS
ON SHIPTRNISSUE.PS_ID = SHIPTRANS.PS_ID
where SHIPTRNISSUE.PS_ID = 'PS-000000002511'

This returns a correct result of      PS_ID                              SO_ISSUE_KEY     SO_ID                  CUST_ID
                                                            PS-000000002511                2698           SO-0001165             TEST

The third table I need to add which contains the remaining detail data is called SO_ISSUE_LN. So as stated bove when querying the PS_ID and selecting the sales order number, the returned amount of records coming from the SO_ISSUE_LN table is 90.

The SO_Issue_LN table has the following fields:

SO_ISSUE_KEY,   SO_LN_KEY,   SO_ID,  PS_ID,   …
0
I'm working in SQL Server 2012 and I'm having difficulty casting varchar as int for comparison between two tables.  

Here's some example DDL/DML:

CREATE TABLE #tbl1 (
      col1 int,
      col2 int
      );
CREATE TABLE #tbl2 (
      col1 varchar(255),
      col2 varchar(255)
      );
INSERT INTO #tbl1 (col1, col2)
VALUES (1, 2);
INSERT INTO #tbl2 (col1, col2)
VALUES (1.0, 2.0);

SELECT * FROM #tbl1 as t1
inner join #tbl2 as t2
on t1.col1=CAST(t2.col1 AS int)
where t1.col2=CAST(t2.col2 AS int)

And the error I'm getting:
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '2.0' to data type int.

If I do this:
SELECT 1.0 AS Original, CAST(1.0 as int) Casted
I get this:
-----------------------------
| Original |Casted |
-----------------------------
|      1.0     |    1       |
------------------------------
which is what I would expect.

I think I'm missing some basic foundational concept here.  Any help will be greatly appreciated.

Thanks!
1
Hi Support

How to transfer the l41 GB sql backup from the RDP server to the local host machine.
0
-- 12.Find the State with the highest Avg UPB.

SELECT  [state],avg(UPB) AS Avg_UPB
FROM Loan
group by [state];


SELECT MAX(AVG(UPB))
FROM Loan
GROUP BY [STATE];

--I get this msg when I execute  it , how do I get the highest avg upb

Msg 4145, Level 15, State 1, Line 247
An expression of non-boolean type specified in a context where a condition is expected, near 'group'.
0
Become a Certified Penetration Testing Engineer
LVL 12
Become a Certified Penetration Testing Engineer

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Sql 2016

I need to find a record(s) that contains two different individuals names as specified by the user.

The person names are stored in the Person Table.
Person Table

Fields:
agency
Year
ID
Sequence (incremented for each individual in a particular record)
Name
Given1
 
The record for individuals would look like this.

Agency   RecordYear    Record ID   Sequence    Name     Given1
1               2018                   1                    1              Allen     John
1               2018                   1                    2             Erwin    Mary
1               2018                   2                    1             Smith    Joe
1               2018                   3                    1             Kline     James
1               2018                   4                    1            Allen     Karen

Currently I have the following sql:

select *
 from Person
      where (name like 'Allen' or name like 'Erwin')
      --where name in ('Allen','Erwin')
      and agency = 1
      and year = 2018

This returns the record I am looking for but also any other record with the name Allen or Erwin.

1               2018    1         1              Allen     John        <----  Should return  this
1               2018    1         2             Erwin     Mary       <----  Should return  this
1               2018     4        1             Allen      Karen      <----  Should not return

I want to only return the record(s) with both …
0
I've been tasked with implementing fail-over clustering for our SQL Servers. There's 4 sites total. Site1, Site2, Site3 and Site4. The question is regarding multiple listeners. Can we setup an AlwaysOn configuration so that each site has a listener? The reason being is if Site1 loses Internet connectivity, each site should still be able to operate and function. I need to be certain that this setup will work or will not work. It boils down to making sure that the database servers would remain operational for the other sites and when Site1 comes back online the synchronization process is started again.
0
I need to call a rest api from my sql server, so I found a way of doing it but to make the code to run I need to enable
Ole Automation Procedures in sql server by running this query

sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE;  
GO  
sp_configure 'Ole Automation Procedures', 1;  
GO  
RECONFIGURE;  
GO  

What I need to know is for sql server database security wise, is it good practise to call web api from a Stored procedure. As I am not a sql server dba or security expert.
I am open in there is any other way also.
Please let me know
0
Hi,

I am trying to backup SQL database from old server (SQL 2008) which is installed on windows server 2008R2 to new server (SQL 2014) which is installed on windows server 2012. The database is large (around 200G). I did the backup using SQL management Studio and I got the backup file. However, I tried to restore in the new server using restore database from management studio (SQL 2014) but I got an error.

Any suggestion how to backup and restore database from SQL 2008 to SQL 2014

Thx
0
Thank you for looking at my question,

I have embedded an image from an Access table that I want to query so that I end up with the options as a string. I want to end up with one line showing, in this case,
GenericItem, Posn, Item, Options showing VE01, 200, 3890602089,SKD0

BOMMatrix Table
Any help you can offer will be much appreciated
1
Hi All

I have a Time Dimension in my SSAS that i need to extend, i have followed the following Article

https://social.technet.microsoft.com/wiki/contents/articles/24675.extensionexpansion-of-time-dimension-in-analysis-services.aspx#Expansion_technique_for_time_dimension_created_using_time_table_generated_in_the_data_Source

but it does nopt seem to work can anybody assist
0

Query Syntax

52K

Solutions

20K

Contributors

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.