sql prefix with numbers

how to add prefix in sql select query
sql prefix values
db2 sql prefix
sql prefix string
sql concatenate
sql substring
left padding in sql server
how to add prefix 0 in sql server

In my select I have multiple dates in one rows, divided by comma.

Main select:

SELECT DISTINCT p.IDVazniZaznam,
stuff(
(
    SELECT ','+ CONVERT(VARCHAR,DatumCasZacatku, 22) FROM HVZHlavicka_Prestavka WHERE IDVazniZaznam = p.IDVazniZaznam FOR XML PATH('')
),1,1,'') As DatumCasZacatku,

stuff(
(
    SELECT ','+ CONVERT(VARCHAR,DatumCasUkonceni, 22) FROM HVZHlavicka_Prestavka WHERE IDVazniZaznam = p.IDVazniZaznam FOR XML PATH('')
),1,1,'') AS DatumCasUkonceni
FROM (SELECT DISTINCT IDVazniZaznam, DatumCasUkonceni, DatumCasZacatku FROM HVZHlavicka_Prestavka ) p

Like this:

12/04/19  7:45:00 AM,12/04/19  8:00:02 AM

What I need to do is something like that:

 1: 12/04/19  7:45:00 AM, 2: 12/04/19  8:00:02 AM

Im not sure if its called prefix, but I think it is. I do not want to put it manually, but I wanna generate it automatically. First date will be number 1, second date number 2 etc. Its that even possible in SQL?

You can use logic from the below code-

DECLARE @text VARCHAR(MAX) = '12/04/19  7:45:00 AM,12/04/19  8:00:02 AM'
DECLARE @remaining_text VARCHAR(MAX) = @text

DECLARE @new_text VARCHAR(MAX) = ''
DECLARE @CommaIndex INT
DECLARE @Count INT = 1

SET @CommaIndex = CHARINDEX(',',@remaining_text,0)

IF @CommaIndex = 0
BEGIN
    SET @new_text = '1: ' + @text
END

ELSE
BEGIN
    WHILE @CommaIndex > 0
    BEGIN
       SET @new_text = @new_text+ ' '+CAST(@Count AS VARCHAR)+': ' + LEFT(@remaining_text,CHARINDEX(',',@remaining_text,0))
       SET @remaining_text = RIGHT(@remaining_text,LEN(@remaining_text)-CHARINDEX(',',@remaining_text,0))

       SET @CommaIndex = CHARINDEX(',',@remaining_text,0)
       SET @Count = @Count + 1
    END
END


IF CHARINDEX(',',@text,0) > 0 
BEGIN
    SET @new_text = @new_text + ' '+CAST(@Count AS VARCHAR)+': ' + @remaining_text
END

SELECT  @new_text,@remaining_text

Output is-

 1: 12/04/19  7:45:00 AM, 2: 12/04/19  8:00:02 AM

add prefix to numeric values – SQLServerCentral, add prefix to numeric values – Learn more on the SQLServerCentral SQL DBA, SQL Server MVP(07, 08, 09) Prosecutor James Blackburn,� For discussion purpose, I always have some suspicion that in some cases, FORMAT() may be better than t-sql, for example, separate large numbers with comma every 3 digits, or formating a numeric with percentage %. I never test it but just of gut feeling, it seems the t-sql is complex to achieve the same result.

You can use STRING_AGG(), ROW_NUMBER() OVER (ORDER BY ..), STRING_SPLIT() and CONCAT() functions together ( STRING_AGG() works for SQL Server 2017+ ).

In the first step we're splitting the string by comma delimiters in the subquery, and then concatenating those substrings through use of STRING_AGG() :

DECLARE @DatumCasUkonceni NVARCHAR(1000) = '12/04/19  7:45:00 AM,12/04/19  8:00:02 AM';

SELECT STRING_AGG(q.str, ',' ) as "Result"
  FROM
  (
   SELECT CONCAT( ROW_NUMBER() OVER (ORDER BY @DatumCasUkonceni), ' : ',
          value ) as str
     FROM STRING_SPLIT(@DatumCasUkonceni, ',')  
  ) q  

Demo

Adding Prefix and Suffix to existing string values? - Databases, My sql is rusty and I need some help with this Basically, I need to add a prefix and a suffix to the contents of a cell for all records. Example. There are many tips and articles out there that solve SQL Server problems using a Numbers table as part of the solution - whether it be an explicit Numbers table, or a set derived from existing objects (like spt_values or sys.all_objects), or even a recursive CTE generated at runtime.

If STRING_AGG is available to your version of MS Sql Server (2017+), then something like this should do the job.

SELECT IDVazniZaznam
, STRING_AGG(CONCAT(rn,': ',DtStrCasZacatku),', ') AS DatumCasZacatku
, STRING_AGG(CONCAT(rn,': ',DtStrUkonceni),', ') AS DatumCasUkonceni
FROM 
(
  SELECT DISTINCT IDVazniZaznam
  , CONVERT(VARCHAR,DatumCasZacatku, 22) AS DtStrCasZacatku
  , CONVERT(VARCHAR,DatumCasUkonceni,22) AS DtStrUkonceni
  , ROW_NUMBER() OVER (PARTITION BY IDVazniZaznam ORDER BY DatumCasZacatku, DatumCasUkonceni) AS rn
  FROM HVZHlavicka_Prestavka
) q
GROUP BY IDVazniZaznam;

And this will also work in MS Sql Server 2012

WITH CTE AS
(
  SELECT DISTINCT IDVazniZaznam
  , DatumCasZacatku
  , DatumCasUkonceni
  , ROW_NUMBER() OVER (PARTITION BY IDVazniZaznam ORDER BY DatumCasUkonceni, DatumCasZacatku) AS rn
  FROM HVZHlavicka_Prestavka
)
SELECT q.IDVazniZaznam
, STUFF(a1.DatumCasZacatku,1,2,'') AS DatumCasZacatku
, STUFF(a2.DatumCasUkonceni,1,2,'') AS DatumCasUkonceni
FROM 
(
  SELECT IDVazniZaznam
  FROM CTE
  GROUP BY IDVazniZaznam
) AS q
OUTER APPLY
(
  SELECT CONCAT(', ',rn,': ', CONVERT(VARCHAR,DatumCasZacatku, 22))
  FROM CTE c
  WHERE c.IDVazniZaznam = q.IDVazniZaznam
  FOR XML PATH ('')
) a1(DatumCasZacatku)
OUTER APPLY
(
  SELECT CONCAT(', ',rn,': ', CONVERT(VARCHAR,DatumCasUkonceni,22))
  FROM CTE c
  WHERE c.IDVazniZaznam = q.IDVazniZaznam
  FOR XML PATH ('')
) a2(DatumCasUkonceni);

Test for both on db<>fiddle here

Computing the prefix from a alphanumeric varchar column in SQL , I wanted to use the prefix in SQL group by and aggregrate functions. In this example, it returns the first occurence of a number in the order� If you’ve ever tried to concatenate a string with a number while using SQL Server, but received an error, this article should clear things up for you.There’s more than one way to perform concatenation using T-SQL in SQL Server, and if you’re concatenating different data types (like a string and a number) then you may receive an error, depending on how you do the concatenation.

SQL Server String Padding - Leading Zeros, SQL left pad number - prefix number with zeros - sql server leading zero. -- SQL convert number to text - cast number to varchar string. SELECT EmployeeID,. Prefix Prefix meaning Sample words; uni-1: unicorn: mythical creature with one horn: mono-1: monorail: train that runs on one track: bi-2: bicycle: two-wheeled vehicle: tri-3: triceratops: three-horned dinosaur: quadr-4: quadruped: four-footed animal: quint-5: quintuplets: five babies born at a single birth: penta-5: pentagon: figure with five

Numeric strings: VARCHAR fields holding numbers, SQL is a type-safe language. Using the wrong data type—e.g. VARCHAR to store numbers—can cause performance problems. How to diagnose and fix them. Sequence Numbers. 03/14/2017; 10 minutes to read +2; In this article. Applies to: SQL Server (all supported versions) Azure SQL Database A sequence is a user-defined schema-bound object that generates a sequence of numeric values according to the specification with which the sequence was created.

Find tables with names with specific prefix in SQL Server database , Query below finds tables which names start with specific prefix, e.g. tables with names starting with 'hr'. Query. select schema_name(t.schema_id) as� The replicate T-SQL function in Microsoft’s SQL Server makes it simple to add leading zeros to a number value. Let’s create a table ‘emp’ with empid and salary columns. create table emp ( empid int, salary int ); Insert some sample data into table ‘emp’. insert into emp values (1,300); insert into emp values […]

Comments
  • @a_horse_with_no_name Add info about DB. Thanks for info. ;)
  • show us your select query
  • Select the items in a subquery with RANK to get the sequence and them concatonate them